2 Replies Latest reply: Apr 13, 2011 10:18 AM by Gerhard Laubscher RSS

    Compare sets of tables

    Gerhard Laubscher

      Hi,

      Firstly, I apologise because this will be a long post - I have to explain the whole situation, so thanks in advance for not being too angry at me..

      Every month I send out a marketing text (SMS) to customers on our databse who meet certain criteria at that time. Usually I will send the message to about 50,000 people (about a third of the customers).

      Then, every week for four weeks I create a report on the purchasing behaviour of the customers to whom I sent the message. My report (which I always did in Excel using vLookup and plenty of patience) matches account numbers of customers who were sent the marketing messages to the account numbers on our weekly transaction extract (we get this extract daily, and then a weekly one on mondays - I always use the weekly ones, but for practise in QV I am now using the daily ones). What I then do is compare the same things BEFORE and AFTER the message, over the same time period.

      For example, if I sent a message on the 7th of March, I will do the following (it will look nicer in the report obviously [:D] :

      So I can see that because of the message (hopefully), the same group of customers made as almost a Million Rand extra over the same time period (4 weeks in this example).

      Okay - now back to QV:

      I load my SMS List. Then I load my transaction extract (every day, but will in the future do this weekly on Mondays, the day when I do the report). This is the script I use:

      LOAD [Cell phone],
      [Account No],
      Title,
      [First name],
      [Last Name],
      OTB,
      Country,
      [Date SMS Sent]
      FROM
      [C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Dunns & DFX\SMS 6 month campaigns\Campaign 2 - 11 April\Reporting\Spend\SMS List.xlsx]
      (ooxml, embedded labels, table is Sheet1);
      LOAD
      Company,
      Product,
      [Account No],
      [Posted Date],
      [Effective Date],
      [Store No],
      [Merchant Code],
      [Store Name],
      [Store Country],
      [Card No],
      [Transaction Type],
      [Tran Code],
      [Trans Desc],
      [Trans Amount],
      [Trans Auth]
      FROM
      [C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Dunns & DFX\SMS 6 month campaigns\Campaign 2 - 11 April\Reporting\Spend\Daily_TransactionExtract_A_11-APR-2011_12042011014626.txt]
      (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)


      WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No]) ;

      concatenate LOAD Company,
      Product,
      [Account No],
      [Posted Date],
      [Effective Date],
      [Store No],
      [Merchant Code],
      [Store Name],
      [Store Country],
      [Card No],
      [Transaction Type],
      [Tran Code],
      [Trans Desc],
      [Trans Amount],
      [Trans Auth]
      FROM
      [C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Dunns & DFX\SMS 6 month campaigns\Campaign 2 - 11 April\Reporting\Spend\Daily_TransactionExtract_A_12-APR-2011_13042011014517.txt]
      (txt, codepage is 1252, embedded labels, delimiter is '~', header is 1 lines)

      WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No]) ;

      This is what I have created so far:

       

      So all my calculations are there (turnover, % spending, etc.). NOW for the tricky part and the reason for this long post:

      In the graph, I want to show the comparison BEFORE and AFTER the message. So there should be 2 bars standing behind each other for each of the bars shown in the graph. That means that in my load script (or somewhere) I need to differentiate between transaction extracts that are BEFORE and transaction extracts that are AFTER the date of the message.

      So I sent a message on Monday (11 April). Next Monday (18th) I will upload the weekly transaction extract for 11-17 April. I will also upload the weekly extract for 4-10 April, and I need to compare them to each other, so that I can come up with a table like the 1st picture in this post, and a graph like in the second picture, but with the comparison. The following Monday (25th) I must compare 2 extracts before (4-10 April and 18March-3April) with 2 extracts after (11-17 April and 18-24 April). The next week 3 before and after, and the final week 4 extracts before and after.

      Okay, anyone who read all of this - thank you and congrats! Anyone who can help me will be my hero forever.

      Regards,

      Gerhard

        • AW:Compare sets of tables

          Hello Gerhard,

          I recognize you as the man with the largest posts ever ;-). As far as I remember this is not your first large post. And I tried to follow you all the way through your description, but I think I hardly can help you without an exam app (at least with scrampled data). So try to post one and I am sure there is someone who can help you.

          Regards, Roland

            • AW:Compare sets of tables
              Gerhard Laubscher

              Hi Roland,

              I know my posts are long and plenty - I started using QlikView only a few days ago and there is SO much to figure out. So apologies for the longs posts.

              Due to the sensitivity of the data (and the fact that the account numbers are my keys) I cannot load the app, but I'll try and get around to creating a file with sample data. I'll do this tomorrow (word day is almost over here in SA).

              In a nutshell, all I need to do is concatenate 4 different transaction extracts (new one every week) for after the text was sent. And then I need to concatenate four old transaction extracts for before the text was sent (these extracts I already have of course). Then I need to compare old Vs. New.

              So extracts "Old 1"+"Old 2"+"Old 3"+ "Old 4" = "OldExtract", and

              extracts "New 1"+"New 2"+"New 3"+"New 4" = "NewExtract".

              On Monday I will compare Old1 with New1, the following Monday Old1+Old2 with New1+New2, and so forth.

              Don't know if it makes sense to anyone....?

              Thanks,

              G