load script calculation

[#1]

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

[#2]

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]

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;

[#4]

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]

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!

[#6]

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]

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]

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!

[#9]

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 · Accepted solution]

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;