topic Re: How load date from line? in QlikView

How load date from line?

vchuprina — Tue, 10 Mar 2015 13:43:04 GMT

I've received files with date inside. Now I want update parser to automatically determine the date.

I have two question:

- How load date from line? I suppose, first I should determine row with date and them drop 'Data as of:'

- On picture below date is '3/1/2015', but this was Sunday and I need to load this data as 'Last Saturday'. Is there any function, that can automatically change date to last Saturday( for example 3/1/2015 - > 2/28/2015)

Thanks,

Vitaliy

Re: How load date from line?

prieper — Tue, 10 Mar 2015 14:21:38 GMT

As you mentioned already: Make a separate load with line number two only and extract the date. (In a second go then you would load the data starting with headers at line #5).

Not quite clear on the date: If it is always Sunday, you may deduct just one day. Else you may work with the function WEEKSTART with parameter offset.

HTH Peter

Re: How load date from line?

anbu1984 — Tue, 10 Mar 2015 15:24:10 GMT

LOAD Subfield(@1,':',2), Date(WeekStart(Date#(Trim(Subfield(@1,':',2)),'MM/DD/YYYY'))+5)

FROM

[Input.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq)

Where Index(@1,'Data as of');

Re: How load date from line?

Gabriel — Wed, 11 Mar 2015 10:50:08 GMT

Hi,

Use WeekStart function, see below, to derive last Saturday's date.

WeekStart(DateField,0,-1)

Hope this helps