Trick need in load statement

[jagannalla]

Hi,

I've  data in this format.

Region	Manufacture	01/01/12	01/02/12
Japan	GrandTotal	20	40
Japan	J&J	30	20
US	GrandTotal	53	85
US	J&J	23	15

- Now I want to display output as in below format either in straight table or line chart.

Month	Region	ExpVal
12-Jan	Japan
12-Feb	Japan	1.333
12-Jan	US
12-Feb	US	0.952

- " Exp Val " calculation is (Current Month - Previous Month)/Previous Month

For eg: In Japan, for Feb -12 (20-40)/20 =1.333

- I had given data for only two months, but I had data for entire year i.e. Jan, Feb, Mar,Apr,,,,,,Dec

- Same formula needs to applicable for all months.

- For Jan month we don't have any data for previous month i.e. Dec. It can consider as Null.

Thanks,

Jagan

[Anonymous]

Jagan

Have yo looked at using the crosstable() function ?

Best Regards,     Bill

[Peter_Cammaert]

First, rotate and serialize this data with a CROSSTABLE prefix. The result will be something like:

Japan     GrandTotal     1/01/2012     20

Japan     GrandTotal     1/02/2012     40

:

Japan     J&J                 1/01/2012     30

:

Then load this list into a new RESIDENT table and combine current with previous record to calculate the relative difference. You can use the previous() function if you drop the base values from your new table, or the peek() function if you keep this column. Check whether the previous row has the same Region and Manufacture.

Best,

Peter

[Peter_Cammaert]

I apologize for nitpicking Bill, but CROSSTABLE is a prefix for a LOAD statement, not a function. Functions can be used in expressions. CROSSTABLE cannot. Nevermind the parentheses and what looks like parameters.

Peter

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[jagannalla]

Jagan,

I tried it but no use. I need result I had shown in image.

For each date, GrandTotal - J&J .

Manufacture is not static it has mulitple values. i.e.

GrandTotal - J1

GrandTotal -A1

....

....

....

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[jagannalla]

Jagan Mohan,

I think we are near to the solution. But I've multiple records for each date and manufactor. Plz check the attached image.

Thanks,

Jagan

[jagan]

Hi,

Can you attach the Qlikview file that you are working.

Regards,

Jagan.