Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vchuprina
Not applicable

How load date from line?

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

1 Solution

Accepted Solutions
anbu1984
Not applicable

Re: How load date from line?

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

3 Replies
prieper
Not applicable

Re: How load date from line?

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

anbu1984
Not applicable

Re: How load date from line?

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

Gabriel
Not applicable

Re: How load date from line?

Hi,

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

WeekStart(DateField,0,-1)

Hope this helps