Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
shayraber
Contributor

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
MVP
MVP

Re: Re: incremental data

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
4 Replies
MVP
MVP

Re: incremental data

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

thanks

MVP
MVP

Re: Re: incremental data

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
Contributor

Re: Re: incremental data

here is a part of my data

shayraber
Contributor

Re: Re: incremental data

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

Community Browser