Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Combine, compare, calculate 2 files

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.

1 Reply
Not applicable

Re: Combine, compare, calculate 2 files

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;

Community Browser