topic Re: YTD files load and calculations?? in QlikView

YTD files load and calculations??

qlikuser222 — Thu, 26 Jul 2018 14:48:18 GMT

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

Re: YTD files load and calculations??

sunny_talwar — Thu, 26 Jul 2018 15:03:35 GMT

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;

Re: YTD files load and calculations??

qlikuser222 — Fri, 17 Aug 2018 13:26:23 GMT

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

Re: YTD files load and calculations??

sunny_talwar — Fri, 17 Aug 2018 13:31:33 GMT

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;