2 Replies Latest reply: Apr 13, 2011 5:09 AM by bandariarun RSS

    Where clause in script not working

    Gerhard Laubscher

      Hi,

      I've got an Excel table with customers to whom I sent a marketing text message. Now I want to take our daily transaction extracts and load them into the same QV file every day, so I can see which of the people I sent an SMS to has made a purchase, etc.

      When I add the following clause to my script, the "From" statement of my transaction extract turns from the correct BLUE color to the normal black font, and then I of course get a "garbage after statement error". Can someone please look at my script and see what I'm doing wrong with my where clause. This is the WHERE clause:

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

      and this is my script:

      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]
      WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No]),

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

      Thanks,

      Gerhard

       

        • Where clause in script not working

          Hi

          Put the WHERE at the last line, after FROM

          Regards

          Anders

          • Where clause in script not working
            bandariarun

            Hi gerhardl ,

            use the below script...

            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]) ;