Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey community,
I have to load some data and transform it before and I dont know how to do it.
I have this
these numbers 1, 2 3 in headers are dates: like 1.9, 2.9
8 is an hour when person started working and other 8 is number of hours that person worked.
I would like to have this
How can I transform it with file wizard?
Appreciate any help. Thank you so much
Hi,
one solution might be:
table1:
CrossTable (Day, Value, 3)
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1558719-340964/ex1.xlsx] (ooxml, embedded labels, header is 1 lines, table is Input);
table2:
LOAD *,
1-Working as [Not working];
LOAD Department,
ID,
Name,
Date,
IterNo() as Time,
-(IterNo()>=HourStart and IterNo()<=HourStart+HourWorked-1) as Working
While IterNo()<=24;
LOAD Department,
ID,
Name,
Value as HourWorked,
Previous(Value) as HourStart,
MakeDate(Year(Today()),Month(Today()),Evaluate(Previous(Day))) as Date
Resident table1
Where not IsNum(Evaluate(Day));
DROP Table table1;
hope this helps
regards
Marco
please post some sample data in a format that can be loaded in QlikView (e.g. CSV, Excel or as table in your post) as well as your expected result for this data.
Thank you for answer. In sheet1 (input) is the table that I have before loading, and in sheet2 is what would I like to have.
Appreciate your help very much.
Hi,
one solution might be:
table1:
CrossTable (Day, Value, 3)
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1558719-340964/ex1.xlsx] (ooxml, embedded labels, header is 1 lines, table is Input);
table2:
LOAD *,
1-Working as [Not working];
LOAD Department,
ID,
Name,
Date,
IterNo() as Time,
-(IterNo()>=HourStart and IterNo()<=HourStart+HourWorked-1) as Working
While IterNo()<=24;
LOAD Department,
ID,
Name,
Value as HourWorked,
Previous(Value) as HourStart,
MakeDate(Year(Today()),Month(Today()),Evaluate(Previous(Day))) as Date
Resident table1
Where not IsNum(Evaluate(Day));
DROP Table table1;
hope this helps
regards
Marco