Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
pbran
New Contributor II

Where to put flags in data loader script?

# The End Goal and Context

I posted here about a calculation formula, and thankfully someone helped, but now I'm having more tough times with conditional operators in the loader script.

https://community.qlik.com/t5/Qlik-Sense-App-Development/Find-Average-from-quot-count-if-quot-and-qu...


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: ','`


----------


Any help would be greatly appreciated! Thank you!

1 Solution

Accepted Solutions

Re: Where to put flags in data loader script?

Try this

[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]);

View solution in original post

3 Replies

Re: Where to put flags in data loader script?

Try this

[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]);

View solution in original post

124psu
Contributor

Re: Where to put flags in data loader script?

This should work.

pbran
New Contributor II

Re: Where to put flags in data loader script?

Sunny, you're a God send. Thank you so much!