Help with data extract transformation

[Anonymous]

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.

[ashfaq_haseeb]

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

like this?

see attachment

[Anonymous]

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]

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

[maxgro]

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]

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:

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:

hope this helps also

regards

Marco

[Anonymous]

Thank you Marco

[MarcoWedel]

You're welcome

regards

Marco