Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Help with data extract transformation

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

like this?

see attachment

1.png

View solution in original post

9 Replies
ashfaq_haseeb
Champion III
Champion III

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

maxgro
MVP
MVP

like this?

see attachment

1.png

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

maxgro
MVP
MVP

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

Anonymous
Not applicable
Author

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

MarcoWedel

Hi,

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

QlikCommunity_Thread_137949_Pic1.JPG.jpg

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%20Works...] (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:

QlikCommunity_Thread_137949_Pic2.JPG.jpg

hope this helps also

regards

Marco

Anonymous
Not applicable
Author

Thank you Marco

MarcoWedel

You're welcome

regards

Marco