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: 
nerakegl
Partner - Contributor II
Partner - Contributor II

File transformation

Hey community,

I have to load some data and transform it before and I dont know how to do it.

I have this

2018-10-12 14_46_47-Book1 - Excel.png

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

2018-10-12 14_47_10-Book1 - Excel.png

How can I transform it with file wizard?

Appreciate any help. Thank you so much

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_316689_Pic1.JPG

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

View solution in original post

3 Replies
MarcoWedel

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.

nerakegl
Partner - Contributor II
Partner - Contributor II
Author

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.

MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_316689_Pic1.JPG

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