topic File transformation in QlikView

File transformation

nerakegl — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: File transformation

MarcoWedel — Sun, 14 Oct 2018 18:58:52 GMT

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.

Re: File transformation

nerakegl — Sun, 14 Oct 2018 19:36:40 GMT

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.

Re: File transformation

MarcoWedel — Mon, 15 Oct 2018 20:29:18 GMT

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