Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
like this?
see attachment
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.
Hi Ashfag, Thanks a lot for your response. This will guide me for sure.
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
I was trying along the same lines man . That was a great help
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
Thank you Marco
You're welcome
regards
Marco