topic csv+where clause+string to date conversion in QlikView

csv+where clause+string to date conversion

Fri, 25 Feb 2011 00:59:52 GMT

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!

csv+where clause+string to date conversion

johnw — Fri, 25 Feb 2011 01:03:29 GMT

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.

csv+where clause+string to date conversion

Fri, 25 Feb 2011 01:14:10 GMT

sir,

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

csv+where clause+string to date conversion

johnw — Fri, 25 Feb 2011 01:23:51 GMT


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?

csv+where clause+string to date conversion

Fri, 25 Feb 2011 01:28:36 GMT

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

csv+where clause+string to date conversion

Fri, 25 Feb 2011 01:30:09 GMT

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

csv+where clause+string to date conversion

Fri, 25 Feb 2011 01:33:30 GMT

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..

csv+where clause+string to date conversion

johnw — Fri, 25 Feb 2011 01:42:15 GMT


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.

csv+where clause+string to date conversion

johnw — Fri, 25 Feb 2011 01:45:13 GMT


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.

csv+where clause+string to date conversion

Fri, 25 Feb 2011 01:58:58 GMT

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!!

csv+where clause+string to date conversion

Fri, 25 Feb 2011 06:43:38 GMT

anyone here knows how to use where clause when loading a csv file??

thanks!

csv+where clause+string to date conversion

Fri, 25 Feb 2011 06:48:37 GMT

this is my where clause:

Where [Date] Between #$(vFromDate)# AND #$(vToDate)#

csv+where clause+string to date conversion

johnw — Fri, 25 Feb 2011 18:47:35 GMT

"#...#" isn't valid QlikView syntax, and neither is "between". Try this:

WHERE Date >= $(vFromDate)
AND Date <= $(vToDate);