topic Re: load script calculation in QlikView

load script calculation

Wed, 10 Dec 2014 14:39:35 GMT

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

Re: load script calculation

Wed, 10 Dec 2014 14:42:08 GMT

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

Re: load script calculation

lironbaram — Wed, 10 Dec 2014 14:47:12 GMT

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;

Re: load script calculation

Wed, 10 Dec 2014 14:49:58 GMT

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

Re: load script calculation

jerem1234 — Wed, 10 Dec 2014 14:55:09 GMT

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!

Re: load script calculation

Wed, 10 Dec 2014 14:57:40 GMT

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.

Re: load script calculation

francoiscave — Wed, 10 Dec 2014 14:58:38 GMT

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

Re: load script calculation

jerem1234 — Wed, 10 Dec 2014 15:01:34 GMT

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!

Re: load script calculation

Wed, 10 Dec 2014 15:03:54 GMT

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-

Re: load script calculation

simenkg — Wed, 10 Dec 2014 15:04:10 GMT

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;

Re: load script calculation

Wed, 10 Dec 2014 15:22:27 GMT

THATS WHAT I NEED.

THANKS.