Search code examples
databasescriptingqliksense

Where to place flags in Qlik Sense data loading script?

The End Goal and Context

The end product in my Qlik sheet should have a table that displays a "% Covered" column. What this is is the percentage of items in a department that the inventory is greater or equal than the requirement.

Here is an example of what the end table should look like (The Percent Covered field is a calculated field:

| Department | Count of Products | Percent Covered |
|------------|-------------------|-----------------|
| Bio        | 34858             | 89.40%          |
| Mech       | 50119             | 92.05%          |
| Admin      | 560               | 98.22%          |

To hopefully illustrate this better, this is what it would look like if you were to apply the calculation to a table of product numbers:

| Product Number | Inventory | Requirement | Percent Covered |
|----------------|-----------|-------------|-----------------|
| 444391         | 112       | 113         | 99.11%          |
| 444569         | 86        | 350         | 24.57%          |
| 443551         | 12        | 11          | 109.09%         |

The Problem

Though it works in testing with small amounts of data, when loaded with the real data it is too much for Qlik to handle in the expressions on the charts themselves and throws timeout errors:

Count({<ProductNumber = {"=[Inventory] >= [Requirement]"}>} ProductNumber) / Count(ProductNumber)

To get around this, I've been trying to do the above work in the data load script. With the help of the Qlik docs and Community Forum, this is apparently something should work: In the data loader script: If([Inventory] >= [Requirement], 1, 0) as Flag In the expression: Count({<Flag = {'1'}>} ProductNumber)/Count(ProductNumber)

The problem is, no matter where I place this in the script, it throws errors.

What I've Tried

I've tried all these combos with and without semicolons.

[MyAwesomeSheet]:
If([Inventory] >= [Requirement], 1, 0) as Flag
LOAD
    [ProductNumber],
    [Requirement],
    [Inventory],
    [Department],
 FROM [lib://Desktop/fake-example-data.xlsx]
(ooxml, embedded labels, table is [MyAwesomeTable]);

Error: "Data has not been loaded. Please correct the error and try loading again."

[MyAwesomeSheet]:
LOAD
    [ProductNumber],
    [Requirement],
    [Inventory],
    [Department],
 FROM [lib://Desktop/fake-example-data.xlsx]
If([Inventory] >= [Requirement], 1, 0) as Flag
(ooxml, embedded labels, table is [MyAwesomeTable]);

Error: "Data has not been loaded. Please correct the error and try loading again."

[MyAwesomeSheet]:
LOAD
    [ProductNumber],
    [Requirement],
    [Inventory],
    [Department],
 FROM [lib://Desktop/fake-example-data.xlsx]
(ooxml, embedded labels, table is [MyAwesomeTable]);
If([Inventory] >= [Requirement], 1, 0) as Flag

Error: "Unexpected token: ','`

Solution

  • Based on what you have tried there are two ways:

    Inside load

    you can create the flag field inside the load statement itself

    [MyAwesomeSheet]:
LOAD
    [ProductNumber],
    [Requirement],
    [Inventory],
    [Department],
    If([Inventory] >= [Requirement], 1, 0) as Flag
 FROM [lib://Desktop/fake-example-data.xlsx]
(ooxml, embedded labels, table is [MyAwesomeTable]);

    Preceding load

    Another technique is using preceding load

    You can have a many preceding loads as you want. Each load is loading data from the preceding loads below it

    [MyAwesomeSheet]:
Load
    *,
    If([Inventory] >= [Requirement], 1, 0) as Flag
LOAD
    [ProductNumber],
    [Requirement],
    [Inventory],
    [Department],
 FROM [lib://Desktop/fake-example-data.xlsx]
(ooxml, embedded labels, table is [MyAwesomeTable]);