Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

load script calculation

Okay guys,

Example:  (Now I need the possibility to calculate the purple header during the load script)

Red part I have already in my source

TABLE XYZ:

Field Header:         Order     |         Product      |         Customer       |     Qty. Products on Order     

Field Content:          A                     small                   John                                      3

                                A                      big                      John                                     3

                                A                      small                    John                                    3

                                B                      small                    Paul                                      1

This works:

TEST:
LOAD
A,
count(A) as QTY_A
RESIDENT TABLE_XYZ
group by A;

A     3

B     1

This doesn't work:

TEST:
LOAD
A,
count(A) as QTY_A,
if(wildmatch(PRODUCT,'small'),count(A),0) as QTY_small,
if(wildmatch(PRODUCT,'big'),count(A),0) as QTY_big
RESIDENT TABLE_XYZ
group by A;

expected result  

A   3    2   1

B   1    1    0

1 Solution

Accepted Solutions
simenkg
Specialist
Specialist

Test:

Load Order, sum(1) as QTY, sum(if(Product='small,1,0) as QTY_small, sum(if(Product='big',1,0) as QTY_big

Resident Table_XYZ

Group by Order;

View solution in original post

10 Replies
Not applicable
Author

Okay guys,  (corrected below script) but still doesn't work

Example:  (Now I need the possibility to calculate the purple header during the load script)

Red part I have already in my source

TABLE XYZ:

Field Header:         Order     |         Product      |         Customer       |     Qty. Products on Order     

Field Content:          A                     small                   John                                      3

                                A                      big                      John                                     3

                                A                      small                    John                                    3

                                B                      small                    Paul                                      1

This works:

TEST:
LOAD
Order,
count(Order) as QTY_Order
RESIDENT TABLE_XYZ
group by Order;

A     3

B     1

This doesn't work:

TEST:
LOAD
Order,
count(Order) as QTY_Order,
if(wildmatch(PRODUCT,'small'),count(Order),0) as QTY_small,
if(wildmatch(PRODUCT,'big'),count(Order),0) as QTY_big
RESIDENT TABLE_XYZ
group by Order;

expected result  

A   3    2   1

B   1    1    0

lironbaram
Partner - Master III
Partner - Master III

try this one

TEST:
LOAD
Order,
count(Order) as QTY_Order,
count(if(PRODUCT='small',Order)) as QTY_small,
count(if(PRODUCT='big',Order)) as QTY_big
RESIDENT TABLE_XYZ
group by Order;

Not applicable
Author

my first post was wrong did already correction below that it also doesn't work

This doesn't work:

TEST:
LOAD
Order,
count(Order) as QTY_Order,
if(wildmatch(PRODUCT,'small'),count(Order),0) as QTY_small,
if(wildmatch(PRODUCT,'big'),count(Order),0) as QTY_big
RESIDENT TABLE_XYZ
group by Order;   it doesn't work


jerem1234
Specialist II
Specialist II

Try:

TEST:

LOAD

Order,

count(Product) as QTY_small

RESIDENT TABLE_XYZ

where Product = 'small'

Group by Order;

Outer Join(TEST)

LOAD

Order,

count(Product) as QTY_big

RESIDENT TABLE_XYZ

where Product = 'big'

Group by Order;

Hope this helps!

Not applicable
Author

Thanks for this was already thinking about this but hoped I could do this with just one Load statement.

mhhhhhh....

Okay unless nobody knows how to put this under one statement I will go with that.

francoiscave
Partner - Creator III
Partner - Creator III

Hi Ivan,

Could you test this :

Data:

LOAD * Inline [

Order,Product,Customer,Qty. Products on Order    

A,small,John,3

A,big ,John,3

A,small,John,3

B,small,Paul,1];

Test:

LOAD

  Order,

  Count(DISTINCT Product) as Qty_Product,

  Count(DISTINCT Customer) as Qty_Customer,

  Count(Order) as Qty_ProductsOnOrder

Resident Data

Group by Order;

DROP Table Data;

François

jerem1234
Specialist II
Specialist II

You could keep as is and just use count() in the dashboard and not the script (I don't know if you had a reason why you wanted to do a count in script). Just use set analysis with Order as dimension, and something like:

count({<Product = {'big'}>}Product)

Or for small:

count({<Product = {'small'}>}Product)

Hope this helps!

Not applicable
Author

I understand that I could use SET analysis in the Dashboard itself, but I want to have as less as possible    set analysis in the script and rather have it directly in the table as an field.

For potential save as QVD again and reuse-

simenkg
Specialist
Specialist

Test:

Load Order, sum(1) as QTY, sum(if(Product='small,1,0) as QTY_small, sum(if(Product='big',1,0) as QTY_big

Resident Table_XYZ

Group by Order;