Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vchuprina
New Contributor II

How load date from file?

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;

Untitled1.pngUntitled.png

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

Tags (1)
1 Solution

Accepted Solutions

Re: How load date from file?

Check out the attached qvw.

Best,

Sunny



10 Replies

Re: How load date from file?

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

vchuprina
New Contributor II

Re: How load date from file?

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

Re: How load date from file?

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

vchuprina
New Contributor II

Re: How load date from file?

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?

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

vchuprina
New Contributor II

Re: How load date from file?

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

Re: How load date from file?

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

vchuprina
New Contributor II

Re: How load date from file?

I want to load date with different format as Date.

For example I want to see following

Untitled.png

And you script shows

Untitled.png

Re: How load date from file?

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

Community Browser