Combine, compare, calculate 2 files

Fri, 18 Jul 2014 22:58:55 GMT

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.

Re: Combine, compare, calculate 2 files

Sat, 19 Jul 2014 05:09:53 GMT

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;

topic Combine, compare, calculate 2 files in QlikView

Combine, compare, calculate 2 files

Re: Combine, compare, calculate 2 files