topic Re: Help with data extract transformation in QlikView

Help with data extract transformation

Anonymous — Wed, 15 Oct 2014 18:21:22 GMT

Hi All i have requirement that in the below attached excel sheet i have column in which the data is in the form of 0's and 1's. Here 1 means a working day and zero means non working day for each year. Is there any way to transform that column data into rows. Please see attached excel sheet.

Re: Help with data extract transformation

ashfaq_haseeb — Wed, 15 Oct 2014 18:38:55 GMT

You may try with generic relaod

have a look at below post

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

Generic

LOAD *

FROM

[New Microsoft Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards

ASHFAQ

Re: Help with data extract transformation

maxgro — Wed, 15 Oct 2014 18:44:47 GMT

like this?

see attachment

Re: Help with data extract transformation

Anonymous — Wed, 15 Oct 2014 19:55:26 GMT

Hi Massimo Grossi, Thanks a lot man you are a champ. i got the output but can you please help me how it works especailly the last script part

load RowNo() as Id,

CAL_ID, CAL_YR,

NewField

where len(trim(NewField)) > 0

..Instead of row number Is there any possibilty i can put day number. for each year if i have a day number that would be a great help.

Re: Help with data extract transformation

Anonymous — Wed, 15 Oct 2014 19:56:11 GMT

Hi Ashfag, Thanks a lot for your response. This will guide me for sure.

Re: Help with data extract transformation

maxgro — Wed, 15 Oct 2014 20:20:09 GMT

excel:

load

RowNo() as Id,

if(CAL_YR <> peek(CAL_YR), 1, peek(Id1) + 1) as Id1,

where len(trim(NewField)) > 0;

load

CAL_ID,

CAL_YR,

SubField(DRP_DAY_WGT_CODE, ',') as NewField;

NoConcatenate load

CAL_ID,

CAL_YR,

replace(replace(DRP_DAY_WGT_CODE, 1, '1,'), 0, '0,') as DRP_DAY_WGT_CODE

Resident tmp

order by CAL_YR, CAL_ID;

starting from first load (bottom)

- replace to add comma (from 1100 to 1,1,0,0,)

- subfield use comma to generate a row for every 1 or 0; comma is the substring separator, substring are 0 or 1

- where len(trim(NewField)) > 0 because subfield create an empty rows; this is the last row by year, due to the - substring (empty) after last comma 1,1,1,1,1,0,0,..............1,1,1,1,0,0,0,1,

- rowno() not needed, just to show the result

if you want a counter from 1 (1st jan) to 365, add another field (bold)

if(CAL_YR <> peek(CAL_YR), 1, peek(Id1) + 1) as Id1

Re: Help with data extract transformation

Anonymous — Wed, 15 Oct 2014 20:32:55 GMT

I was trying along the same lines man . That was a great help

Re: Help with data extract transformation

MarcoWedel — Wed, 15 Oct 2014 20:52:05 GMT

Hi,

even though this thread is already closed, I wanted to post the solution I had already started to work on:

LOAD CAL_ID,
    CAL_YR,
    Dual(If(not Mid(DRP_DAY_WGT_CODE,IterNo(),1),'non ')&'working day',Mid(DRP_DAY_WGT_CODE,IterNo(),1)) as WorkDay,
    Date(MakeDate(CAL_YR,1,1)+IterNo()-1) as Date
FROM [http://community.qlik.com/servlet/JiveServlet/download/634888-132381/New%20Microsoft%20Excel%20Worksheet.xlsx] (ooxml, embedded labels, table is Sheet1)
While InYear(MakeDate(CAL_YR,1,1)+IterNo()-1,MakeDate(CAL_YR,1,1),0);

Dual loading the WorkDay field to be able to use it like:

hope this helps also

regards

Marco

Re: Help with data extract transformation

Anonymous — Tue, 28 Oct 2014 15:04:45 GMT

Thank you Marco

Re: Help with data extract transformation

MarcoWedel — Tue, 28 Oct 2014 15:46:40 GMT

You're welcome

regards

Marco