topic Re: incremental data in QlikView

incremental data

shayraber — Thu, 13 Feb 2014 11:20:39 GMT

Hi,

I'm facing an issue while trying to create (in the script, not at the GUI) the following:

I have a table with few dimensions and a value field.

I wish to create a new table with incremental value as the new data for each key record (combination of all the dimensions). I'll try to explain with an image. the image shows 2 tables (the left one is the "before" table which I have and the right one is the "after" table which I wish to have).

In the image I've putted a dummy data just for the explanation of my problem (I've highlighted as an example cells which sums into a new cell at the result table).

Please assist.

Thanks and best regards,

Shay

Re: incremental data

maxgro — Thu, 13 Feb 2014 11:35:53 GMT

could you post your data (excel, txt, ....)?

thanks

Re: Re: incremental data

maxgro — Thu, 13 Feb 2014 11:58:51 GMT

SCRIPT

Directory;

Source:

LOAD YearMonth,

Year,

Month,

CompID,

Item,

Value

FROM

[107297.xlsx]

(ooxml, embedded labels, table is Sheet1);

Table:

NoConcatenate

LOAD YearMonth,

Year,

Month,

CompID,

Item,

Value,

if(Item<>peek(Item) or CompID<>peek(CompID), Value, peek(RollingValue) + Value) as RollingValue

Resident Source

order by Item, CompID, Year, Month;

DROP Table Source;

RESULT

YearMonth	Year	Month	CompID	Item	Value	RollingValue
	2013	1	111	A	1284	1284
	2013	1	111	B	572	572
	2013	1	222	A	536	536
	2013	1	222	B	822	822
	2013	2	111	A	1167	2451
	2013	2	111	B	1170	1742
	2013	2	222	A	780	1316
	2013	2	222	B	916	1738
	2013	3	111	A	1157	3608
	2013	3	111	B	1282	3024
	2013	3	222	A	1479	2795
	2013	3	222	B	1269	3007
	2013	4	111	A	868	4476
	2013	4	111	B	1003	4027
	2013	4	222	A	1004	3799
	2013	4	222	B	773	3780
	2013	5	111	A	1268	5744
	2013	5	111	B	669	4696
	2013	5	222	A	1255	5054
	2013	5	222	B	1222	5002
	2013	6	111	A	608	6352
	2013	6	111	B	1237	5933
	2013	6	222	A	723	5777
	2013	6	222	B	715	5717

Re: Re: incremental data

shayraber — Thu, 13 Feb 2014 12:14:46 GMT

here is a part of my data

Re: Re: incremental data

shayraber — Thu, 13 Feb 2014 12:23:30 GMT

hi Massimo,

I'm sorry but it doesn't work for some reason.

I'm attaching here an excel file with both the source table and the result.

I have filtered the data in order to show an example why the result is not as expected.

i don't what's the reason to get different result than you

It works!!

thanks

i needed to add another code as can be seen at the screen shot below

Thanks Massimo