Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shayraber
Creator
Creator

incremental data

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

Untitled.png

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

YearMonthYearMonthCompIDItemValueRollingValue
20131111A12841284
20131111B572572
20131222A536536
20131222B822822
20132111A11672451
20132111B11701742
20132222A7801316
20132222B9161738
20133111A11573608
20133111B12823024
20133222A14792795
20133222B12693007
20134111A8684476
20134111B10034027
20134222A10043799
20134222B7733780
20135111A12685744
20135111B6694696
20135222A12555054
20135222B12225002
20136111A6086352
20136111B12375933
20136222A7235777
20136222B7155717

View solution in original post

4 Replies
maxgro
MVP
MVP

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

thanks

maxgro
MVP
MVP

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

YearMonthYearMonthCompIDItemValueRollingValue
20131111A12841284
20131111B572572
20131222A536536
20131222B822822
20132111A11672451
20132111B11701742
20132222A7801316
20132222B9161738
20133111A11573608
20133111B12823024
20133222A14792795
20133222B12693007
20134111A8684476
20134111B10034027
20134222A10043799
20134222B7733780
20135111A12685744
20135111B6694696
20135222A12555054
20135222B12225002
20136111A6086352
20136111B12375933
20136222A7235777
20136222B7155717
shayraber
Creator
Creator
Author

here is a part of my data

shayraber
Creator
Creator
Author

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 MassimoUntitled.png