topic Where clause in script not working in QlikView

Where clause in script not working

gerhardl — Wed, 13 Apr 2011 08:58:50 GMT

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

(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

(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines);

Thanks,

Gerhard

Where clause in script not working

Wed, 13 Apr 2011 09:04:12 GMT

Put the WHERE at the last line, after FROM

Regards

Anders

Where clause in script not working

Wed, 13 Apr 2011 09:09:44 GMT

Hi gerhardl ,

use the below script...

LOAD [Cell phone],
[Account No],
Title,
[First name],
[Last Name],
OTB,
Country,
[Date SMS Sent]
FROM

(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

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