Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
nerakegl
Partner
Partner

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
Partner
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

View solution in original post