Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
bwisenosimenkg
Valued Contributor

Re: load script calculation

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;

10 Replies
Not applicable

Re: load script calculation

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
Honored Contributor II

Re: load script calculation

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

Re: load script calculation

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
Valued Contributor II

Re: load script calculation

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

Re: load script calculation

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
Contributor III

Re: load script calculation

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
Valued Contributor II

Re: load script calculation

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

Re: load script calculation

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-

bwisenosimenkg
Valued Contributor

Re: load script calculation

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;

Community Browser