Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a daily .txt file showing the unbilled amount on each unduplicated account:
Date Account Unbilled Bucket
3/31/14 3500170 153 C
3/31/14 3500218 432 A
3/31/14 3500787 128 A
3/31/14 3500345 237 B
The next day, the file looks like this:
Date Account Unbilled Bucket
4/1/14 3500080 129 A
4/1/14 3500218 432 A
4/1/14 3500787 228 B
4/1/14 3500345 181 B
How would I create the following output by loading these files in a Qlik model:
Account Unbilled Change Effect Bucket
3500170 0 -153 removed
3500080 129 129 added A
3500218 432 0 equal A
3500787 228 100 increased B
3500345 181 -56 decreased B
Can I load, compare, and calculate between 2 files like this? I'm familiar with comparison code like CASE/IF and I can make a QVD file each day.
OK, after trying a couple of things I found in this Community, I have a solution:
File1:
LOAD [Date],
[Account],
[Unbilled],
[Bucket]
FROM
[... first qvd file]
(qvd);
outer join LOAD [Date] AS Date2,
[Account]
[Unbilled] AS Unbilled2,
[Bucket] AS Bucket2
FROM
[... second qvd file]
(qvd);
File2:
LOAD *,
Alt(Unbilled2,0)-Alt(Unbilled,0) AS Change,
If(Unbilled2=Unbilled,'equal',
If(Alt(Unbilled,0)=0,'added',
If(Alt(Unbilled2,0)=0,'removed',
If(Unbilled2>Unbilled,'increased',
If(Unbilled>Balance2,'decreased'))))) AS Effect
Resident File1;
drop Tables File1;