// Qlik Sense scripting for data loading
// Data loading script example

// Connect to SQL database
ODBC CONNECT TO [SQL_SERVER_CONNECTION];

// Load sales data
LOAD 
    OrderID,
    CustomerID,
    Date(OrderDate) as OrderDate,
    ProductID,
    Quantity,
    UnitPrice,
    Quantity * UnitPrice as Revenue,
    Year(OrderDate) as OrderYear,
    Month(OrderDate) as OrderMonth,
    Weekday(OrderDate) as OrderWeekday
FROM Orders;

// Load customer data
LOAD 
    CustomerID,
    CustomerName,
    Country,
    Region,
    CustomerSegment
FROM Customers;

// Load product data
LEFT JOIN (Orders)
LOAD 
    ProductID,
    ProductName,
    Category,
    SubCategory,
    Supplier
FROM Products;

// Create calendar
TempCalendar:
LOAD
    Date(MinDate + RowNo() - 1) as TempDate,
    Year(MinDate + RowNo() - 1) as Year,
    Month(MinDate + RowNo() - 1) as Month,
    Week(MinDate + RowNo() - 1) as Week,
    Weekday(MinDate + RowNo() - 1) as Weekday
WHILE MinDate + RowNo() - 1 <= MaxDate;

LOAD
    Min(OrderDate) as MinDate,
    Max(OrderDate) as MaxDate
RESIDENT Orders;

// Rename calendar field
Calendar:
LOAD
    TempDate as OrderDate,
    Year,
    Month,
    Week,
    Weekday
RESIDENT TempCalendar;

DROP TABLE TempCalendar;

// Custom Qlik Sense extension example
define([
    "qlik",
    "jquery",
    "text!./template.html",
    "css!./style.css"
], function(qlik, $, template) {
    'use strict';

    return {
        template: template,
        initialProperties: {
            qHyperCubeDef: {
                qDimensions: [],
                qMeasures: [],
                qInitialDataFetch: [{ qWidth: 10, qHeight: 50 }]
            }
        },
        definition: {
            type: "items",
            component: "accordion",
            items: {
                dimensions: {
                    uses: "dimensions",
                    min: 1,
                    max: 2
                },
                measures: {
                    uses: "measures", 
                    min: 1,
                    max: 3
                },
                sorting: {
                    uses: "sorting"
                },
                settings: {
                    uses: "settings",
                    items: {
                        customSettings: {
                            type: "items",
                            label: "Custom Settings",
                            items: {
                                showTitle: {
                                    type: "boolean",
                                    label: "Show Title",
                                    ref: "props.showTitle",
                                    defaultValue: true
                                },
                                colorScheme: {
                                    type: "string",
                                    component: "dropdown",
                                    label: "Color Scheme",
                                    ref: "props.colorScheme",
                                    options: [
                                        { value: "default", label: "Default" },
                                        { value: "custom", label: "Custom" }
                                    ],
                                    defaultValue: "default"
                                }
                            }
                        }
                    }
                }
            }
        },
        paint: function($element, layout) {
            const app = qlik.currApp(this);
            const data = layout.qHyperCube.qDataPages[0].qMatrix;
            
            // Clear previous content
            $element.empty();
            
            // Create custom visualization
            this.createCustomChart($element, data, layout);
            
            return qlik.Promise.resolve();
        },
        
        createCustomChart: function($element, data, layout) {
            // Custom D3.js visualization
            const margin = {top: 20, right: 20, bottom: 30, left: 40};
            const width = $element.width() - margin.left - margin.right;
            const height = $element.height() - margin.top - margin.bottom;
            
            // Create SVG
            const svg = d3.select($element[0])
                .append("svg")
                .attr("width", width + margin.left + margin.right)
                .attr("height", height + margin.top + margin.bottom)
                .append("g")
                .attr("transform", `translate(${margin.left},${margin.top})`);
            
            // Process data for visualization
            const processedData = data.map(row => ({
                dimension: row[0].qText,
                measure: row[1].qNum,
                elementNumber: row[0].qElemNumber
            }));
            
            // Create scales
            const xScale = d3.scaleBand()
                .domain(processedData.map(d => d.dimension))
                .range([0, width])
                .padding(0.1);
                
            const yScale = d3.scaleLinear()
                .domain([0, d3.max(processedData, d => d.measure)])
                .range([height, 0]);
            
            // Create bars with Qlik Sense selections
            svg.selectAll(".bar")
                .data(processedData)
                .enter().append("rect")
                .attr("class", "bar")
                .attr("x", d => xScale(d.dimension))
                .attr("width", xScale.bandwidth())
                .attr("y", d => yScale(d.measure))
                .attr("height", d => height - yScale(d.measure))
                .style("fill", layout.props.colorScheme === "custom" ? "#1f77b4" : "#069")
                .on("click", (d) => {
                    // Make selections in Qlik Sense
                    app.field(layout.qHyperCube.qDimensionInfo[0].qFallbackTitle)
                       .selectValues([d.elementNumber], true, true);
                });
            
            // Add axes
            svg.append("g")
                .attr("transform", `translate(0,${height})`)
                .call(d3.axisBottom(xScale));
                
            svg.append("g")
                .call(d3.axisLeft(yScale));
        }
    };
});

# Qlik Sense Cloud API integration
import requests
import json
from datetime import datetime

class QlikSenseCloudAPI:
    def __init__(self, tenant_url, api_key):
        self.base_url = f"https://{tenant_url}.qlikcloud.com"
        self.headers = {
            'Authorization': f'Bearer {api_key}',
            'Content-Type': 'application/json'
        }
    
    def get_apps(self):
        """Get list of all apps"""
        response = requests.get(
            f"{self.base_url}/api/v1/apps",
            headers=self.headers
        )
        return response.json()
    
    def create_app(self, name, description=""):
        """Create new Qlik Sense app"""
        app_data = {
            'name': name,
            'description': description,
            'spaceId': 'personal'  # or specific space ID
        }
        
        response = requests.post(
            f"{self.base_url}/api/v1/apps",
            headers=self.headers,
            json=app_data
        )
        return response.json()
    
    def upload_data(self, app_id, file_path, file_name):
        """Upload data file to app"""
        files = {
            'file': (file_name, open(file_path, 'rb'), 'text/csv')
        }
        
        headers_upload = {
            'Authorization': self.headers['Authorization']
        }
        
        response = requests.post(
            f"{self.base_url}/api/v1/apps/{app_id}/data/files",
            headers=headers_upload,
            files=files
        )
        return response.json()
    
    def execute_script(self, app_id, script):
        """Execute data load script"""
        script_data = {
            'script': script
        }
        
        response = requests.put(
            f"{self.base_url}/api/v1/apps/{app_id}/script",
            headers=self.headers,
            json=script_data
        )
        
        if response.status_code == 204:
            # Trigger reload
            reload_response = requests.post(
                f"{self.base_url}/api/v1/apps/{app_id}/reloads",
                headers=self.headers
            )
            return reload_response.json()
        
        return response.json()
    
    def create_visualization(self, app_id, viz_config):
        """Create visualization object"""
        response = requests.post(
            f"{self.base_url}/api/v1/apps/{app_id}/objects",
            headers=self.headers,
            json=viz_config
        )
        return response.json()
    
    def get_app_data(self, app_id, object_id):
        """Get data from app object"""
        response = requests.get(
            f"{self.base_url}/api/v1/apps/{app_id}/objects/{object_id}/data",
            headers=self.headers
        )
        return response.json()

# Usage example
qlik_api = QlikSenseCloudAPI('your-tenant', 'your-api-key')

# Create new app
app = qlik_api.create_app('Sales Analytics', 'Comprehensive sales analysis')
app_id = app['id']

# Upload data
data_file = qlik_api.upload_data(app_id, 'sales_data.csv', 'sales_data.csv')

# Create data load script
load_script = """
LOAD 
    Date,
    [Product Category],
    [Sales Amount],
    Region,
    [Customer Segment]
FROM [lib://DataFiles/sales_data.csv] 
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
"""

# Execute script
reload_result = qlik_api.execute_script(app_id, load_script)

# Create visualization
chart_config = {
    "qInfo": {
        "qType": "barchart"
    },
    "qHyperCubeDef": {
        "qDimensions": [
            {
                "qDef": {
                    "qFieldDefs": ["Product Category"],
                    "qLabel": "Product Category"
                }
            }
        ],
        "qMeasures": [
            {
                "qDef": {
                    "qDef": "Sum([Sales Amount])",
                    "qLabel": "Total Sales"
                }
            }
        ],
        "qInitialDataFetch": [
            {"qWidth": 2, "qHeight": 50}
        ]
    }
}

visualization = qlik_api.create_visualization(app_id, chart_config)
print(f"Created visualization: {visualization}")