1 Reply Latest reply: Jul 19, 2014 1:13 AM by Guy Rivers RSS

    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.

        • 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;