Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser222
Creator
Creator

YTD files load and calculations??

Hi Team,

We have a requirement where we will receive YTD files on monthly basis and we need to create a views on Qlik accordingly.

For example:

File1:

Date  ID  Amount

Jan    1     100

File2:

Date ID  Amount

Feb  ID   120

in the  File2 actual Amount for Feb is 20, because its  a YTD file it should 120 i.e Jan + Feb amount (100+20) 120.

Please find attached docs which will explain the requirement correctly.


i need help with the script part, where i want to take care of this logic in the back end rather than the front end using incremental load, because of huge volume of data and performance issues. (when we select Feb it should show sum(Amount) as 20)


Thanks

1 Solution

Accepted Solutions
sunny_talwar

Try this

Jan:

LOAD Date,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Jan);


Concatenate

Feb:

LOAD Date,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Feb);


Concatenate

mar:

LOAD Date,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Mar);


FinalTable:

NoConcatenate

LOAD ID,

Date,

If(ID = Previous(ID), Amount - Previous(Amount), Amount) as Amount

Resident Jan

Order By ID, Date;


DROP Table Jan;

View solution in original post

3 Replies
sunny_talwar

Try this

Jan:

LOAD Date,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Jan);


Concatenate

Feb:

LOAD Date,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Feb);


Concatenate

mar:

LOAD Date,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Mar);


FinalTable:

NoConcatenate

LOAD ID,

Date,

If(ID = Previous(ID), Amount - Previous(Amount), Amount) as Amount

Resident Jan

Order By ID, Date;


DROP Table Jan;

qlikuser222
Creator
Creator
Author

Hi stalwar1‌,

Thanks for earlier response

YearMonth   ID   Amount

2017-11         1   1900

YearMonth   ID   Amount

2017-12         1   2000

YearMonth   ID   Amount

2018-01         1   100

YearMonth   ID   Amount

2018-02         1   200

In the above case, how can we calculate YTD using your code? ID resets to original amount with new year

Results should be for ID 1

2017 11= 1900

2017 12 = 100

201801 = 100

201802=100

Thanks

sunny_talwar

Have not tried it... but something like this

Jan:

LOAD Date,

   Year(Date) as Year,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Jan);

Concatenate

Feb:

LOAD Date,

   Year(Date) as Year,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Feb);

Concatenate

mar:

LOAD Date,

    Year(Date) as Year,

    ID,

    Amount

FROM

[test.xls.xlsx]

(ooxml, embedded labels, table is Mar);

FinalTable:

NoConcatenate

LOAD ID,

Date,

Year,

If(ID = Previous(ID) and Year = Previous(Year), Amount - Previous(Amount), Amount) as Amount

Resident Jan

Order By ID, Date;

DROP Table Jan;