topic Re: sort in script in QlikView

sort in script

Sun, 10 Feb 2013 16:06:13 GMT

Hi,

I would like to create a cumulative values of [Adunt Impacts] using the following script:

RangeSum([Adult Impacts],Peek('Cumulative_AdsImpact')) as Cumulative_AdsImpact

However to make sense of it my date need to be sorted by DateTime field before cumulation.

How can I sort my data in script?

Any ideas pleae?

Thanks

Re: sort in script

nagaiank — Sun, 10 Feb 2013 16:17:49 GMT

Use the following example script

Data:

LOAD * Inline [

Date, Adult Impacts

1/2/2013,10

1/4/2013,20

1/3/2013,25

1/1/2013,5

];

Result:

NoConcatenate LOAD Date, [Adult Impacts]

,RangeSum([Adult Impacts],Peek([Cumulative Adult Impacts])) as [Cumulative Adult Impacts]

Resident Data Order By Date;

Sample application is attached.

Re: sort in script

Sun, 10 Feb 2013 16:41:09 GMT

Does it mean I have to manually write all Dates and Impacts?

Re: sort in script

nagaiank — Sun, 10 Feb 2013 16:52:12 GMT

No. If you have your data in a table named 'Data', you may use it in the second load.

For the sample application to illustrate the script, I loaded data as Inline load.

Re: sort in script

Sun, 10 Feb 2013 17:00:22 GMT

I have this table ConsData with data loaded already:

ConsData:

Load

Date,

[Adult Impacts],

...

So below that I need to type your code?

NoConcatenate LOAD Date, [Adult Impacts]

,RangeSum([Adult Impacts],Peek([Cumulative Adult Impacts])) as [Cumulative Adult Impacts]

Resident Data Order By Date;

Sorry but I am quiet new to it.

Re: sort in script

Gysbert_Wassenaar — Sun, 10 Feb 2013 17:56:56 GMT

Like this:

ConsData:

Load

Date,

[Adult Impacts],

....

ResultData:

NoConcatenate LOAD

Date,

[Adult Impacts],

RangeSum([Adult Impacts],Peek([Cumulative Adult Impacts])) as [Cumulative Adult Impacts],

...

Resident ConsData Order By Date;

Drop table ConsData; // ConsData is no longer needed, all the data is now in table ResultData

Re: sort in script

Sun, 10 Feb 2013 18:17:52 GMT

Thanks Gysbert for detailed explenation. It was really helpful to get the idea.