csv+where clause+string to date conversion

[#1]

Hi,

I have a few questions to guys, I hope you can help on this:

attached is the csv file(in txt format cause the attachment doesn't support csv file extension)..

1. I have a csv file(read only) thats have date field(actually its a string). I want to convert it to date, I used date#() but nothing happens.

here is my script

LOAD [User ID],
[First Name],
[Last Name],
Department,
Date#(Date,'M/D/YYYY') as LogDate,
Time,
Event,
Terminal,
Remark
FROM
\\Phoenix\Qlik\report.csv
(txt, utf8, embedded labels, delimiter is ',');

2. how or where will i put the where clause in that load statement? because i want to retrieve the data between a date range.

Thanks!

[johnw · Accepted solution]

1) date(date#(Date,'M/D/YYYY')) as LogDate

2) Between the last paren and the semicolon, I believe. If that doesn't work, you can always do a preceeding load, but I assume it works there. I've never loaded from a csv.

[#3]

sir,

I tried your suggestion and it returned null values...

[johnw]

mOngkz wrote:I tried your suggestion and it returned null values...

Worked for me. I just downloaded your file, copied your script, removed the Remark column since it isn't in your file, changed the file name and location to the name and location on my PC, and changed the expression to the one I gave you. Perhaps your sample file does not match your actual file?

[#5]

with regards to where clause qlikview pops a message telling that its a garbage after my load statement

[#6]

maybe thats one factor because this forum doesn't allow me to attach a file with a csv extension.

[#7]

another thing sir,

when I run the debugger the value of this variable is null:

Let me = Peek('LogDate', 0, 'DTR');

why is that so?? sorry if i have many questions..

[johnw]

mOngkz wrote: when I run the debugger the value of this variable is null:
Let me = Peek('LogDate', 0, 'DTR');
why is that so??

I believe only the field name is supposed to be in quotes.

[johnw]

mOngkz wrote:with regards to where clause qlikview pops a message telling that its a garbage after my load statement

Again, I tried it, and it works for me. I've attached my file in case it's helpful. The test.csv file is just your .txt file with the extension changed back to .csv.

[#10]

Thank you for helping me sir john.

your first suggestion worked and i also changed the DateFormat that Qlikview have in the Edit Script window..

my only problem now is the where clause..

Thank you so much!!