topic Re: How load date from file? in QlikView

How load date from file?

vchuprina — Fri, 12 Jun 2015 12:01:25 GMT

Hi all,

I received files and need to load data from them, but have some problems:

1. Data located in different columns and columns(A-C) and lines (3-4)

I use following code for line 3 and the same for line 4 (WHERE recno() = 2; )

How check two lines by one load?

LOAD

num(subfield(rtrim(A & ' ' & B & ' ' & C & ' ' & D ), ' ', -1)) AS Week1,

FROM [$(vFile)] (ooxml, no labels, header is 2 lines, table is [$(vSheetName)])

WHERE recno() = 1;

2. From time to time data has different format (see screenshot), for example

05/02/2015 09:44:35

WE 6.06.15

I tried use following code to load data to variable

LET vWEEK1 = replace(replace(peek('Week1', -1, 'Date1'),'WE',''),'.','/');

weekstart(date('$(vWEEK1)','YYYYMMDD'),0,-2) AS Week

But in this case for some reason I lose data in date format (05/02/2015 09:44:35) and load only WE 6.06.15,

if I use

LET vWEEK1 =(peek('Week1', -1, 'Date1')

I load only 05/02/2015 09:44:35 and lose WE 6.06.15,

Also I tried use 'IF' , but it doesn't work correctly and return only WE 6.06.15 data :

LET vWEEK1 = IF (Wildmatch(peek('Week1', -1, 'Date1'), '*WE*'), replace(replace(peek('Week1', -1, 'Date1'),'WE',''),'.','/'),peek('Week1', -1, 'Date1'));

Thanks,

Vitaliy

Re: How load date from file?

sunny_talwar — Fri, 12 Jun 2015 12:12:59 GMT

May be try it with Alt:

LET vWEEK1 = Alt(Date#(Replace(Replace(Peek('Week1', -1, 'Date1'),'WE',''),'.','/'), 'M.DD.YY'), Date#(Peek('Week1', -1, 'Date1'), 'MM/DD/YYYY hh:mm:ss'))

Re: How load date from file?

vchuprina — Fri, 12 Jun 2015 12:23:33 GMT

I tried to use you code, but still have the same result

Re: How load date from file?

sunny_talwar — Fri, 12 Jun 2015 12:29:21 GMT

So you are using variable because you are checking the format for a single date, right? or do you have a column with different date formats and you are trying to force them into a date format???

Re: How load date from file?

vchuprina — Fri, 12 Jun 2015 12:53:25 GMT

I'm using variable because date under main data and I can't load all necessary information by one load.

Date in 3 or 4 lines and data start from 9 line.

Date comes in different format from week to week (randomly) and I'm trying load date as Date each week without any manual work.

05/02/2015 09:44:35

05/09/2015 09:40:27

WE 6.06.15

Re: How load date from file?

sunny_talwar — Fri, 12 Jun 2015 13:08:49 GMT

Tried this script, seems to work:

Table1:

LOAD Date as Date1

FROM

Community_168133.xlsx

(ooxml, embedded labels, table is Sheet1);

LET vWEEK1 = Alt(Date(Date#(Trim(Replace(Peek('Date2', -1, 'Table2'),'WE','')), 'M.DD.YY'), 'MM/DD/YYYY'), Date(Peek('Date1', -1, 'Table1'), 'MM/DD/YYYY'));

Table2:

LOAD Date as Date2

FROM

Community_168133.xlsx

(ooxml, embedded labels, table is Sheet2);

LET vWEEK2 = Alt(Date(Date#(Trim(Replace(Peek('Date2', -1, 'Table2'),'WE','')), 'M.DD.YY'), 'MM/DD/YYYY'), Date(Peek('Date1', -1, 'Table1'), 'MM/DD/YYYY'));

Re: How load date from file?

vchuprina — Fri, 12 Jun 2015 15:55:59 GMT

Thank you for you help, but this doesn't work too(

Re: How load date from file?

sunny_talwar — Fri, 12 Jun 2015 15:58:13 GMT

Would you be able to share a sample where it doesn't work?

Re: How load date from file?

vchuprina — Fri, 12 Jun 2015 16:26:19 GMT

I want to load date with different format as Date.

For example I want to see following

And you script shows

Re: How load date from file?

sunny_talwar — Fri, 12 Jun 2015 16:30:35 GMT

That's what I asked you intially. I never touched the field itself. I was working on the variable. If you check the variable overview, you will see the correct format. So you need the field to be fixed.

Let me get you that.

Best,

Sunny

Re: How load date from file?

sunny_talwar — Fri, 12 Jun 2015 16:33:23 GMT

Check out the attached qvw.

Best,

Sunny