topic Re: Trick need in load statement in QlikView

Trick need in load statement

jagannalla — Mon, 24 Feb 2014 08:36:39 GMT

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

Re: Trick need in load statement

Anonymous — Mon, 24 Feb 2014 08:52:19 GMT

Jagan

Have yo looked at using the crosstable() function ?

Best Regards, Bill

Re: Trick need in load statement

Peter_Cammaert — Mon, 24 Feb 2014 09:02:32 GMT

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

Re: Trick need in load statement

Peter_Cammaert — Mon, 24 Feb 2014 09:10:17 GMT

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

Re: Trick need in load statement

jagan — Mon, 24 Feb 2014 09:39:57 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Re: Trick need in load statement

jagannalla — Mon, 24 Feb 2014 14:50:19 GMT

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

....

Re: Re: Re: Trick need in load statement

jagan — Tue, 25 Feb 2014 01:42:27 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Re: Re: Re: Trick need in load statement

jagannalla — Tue, 25 Feb 2014 09:06:56 GMT

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

Re: Trick need in load statement

jagan — Wed, 26 Feb 2014 02:15:25 GMT

Hi,

Can you attach the Qlikview file that you are working.

Regards,

Jagan.