Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
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;