How load date from line?

[vchuprina]

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

[prieper]

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 · Accepted solution]

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]

Hi,

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

WeekStart(DateField,0,-1)

Hope this helps