topic Re: Re: Cross table - need help plz.. in QlikView

Cross table - need help plz..

lavanya14 — Sat, 18 Oct 2014 12:51:11 GMT

Hi All,

I have data as mentioned in excel.. Using cross table i have created mutliple lines.But would like to reduce the no of lines as mentioned clearly in excel.

I want to reduce the no of lines when a person like abc is continuously working as driver.

Please refer the excel..

Any suggestions would be greatly appreciated..

Thanks in advance.

Re: Cross table - need help plz..

MK_QSL — Sat, 18 Oct 2014 13:11:36 GMT

Here you go !!!

Re: Cross table - need help plz..

MarcoWedel — Sat, 18 Oct 2014 14:14:07 GMT

Hi,

one solution could be:

tabInput:
CrossTable (Time, Role, 2)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/637232-132886/Sample%20data.xlsx] (ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 12)),Remove(Row, Pos(Top, 11)),Remove(Row, Pos(Top, 10)),Remove(Row, Pos(Top, 9)),Remove(Row, Pos(Top, 8)),Remove(Row, Pos(Top, 7)),Remove(Row, Pos(Top, 6)),Remove(Row, Pos(Top, 5)),Remove(Row, Pos(Top, 4))));

Left Join (tabInput)
LOAD Name, equipment, Time,
    If(Role='driver' and (Previous(Role)<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), 'time start') as TimeTypeTemp
Resident tabInput
Order By Name, equipment, Time;

Left Join (tabInput)
LOAD Name, equipment, Time,
    If(Role='driver' and (Previous(Role)<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), 'time end', TimeTypeTemp) as TimeType
Resident tabInput
Order By Name, equipment, Time desc;

tabVehicleDriverUsage:
Generic LOAD Name, equipment, TimeType, Time(Num#(Time)) Resident tabInput;

DROP Table tabInput;

hope this helps

regards

Marco

Re: Cross table - need help plz..

Anonymous — Sat, 18 Oct 2014 14:55:56 GMT

Hi,

Try below script,

table1:
CrossTable (Time, Value,2)
LOAD *
FROM
Sample data.xlsx
(ooxml, embedded labels, table is Sheet1);


table2:
NoConcatenate
LOAD
  Name,
  equipment,
  Time(Max(Num#(Time))) as EndTime,
  Time(Min(Num#(Time))) as StartTime
Resident table1
Where Value='driver'
Group By Name,equipment;

DROP Table table1;

Thanks

Re: Cross table - need help plz..

lavanya14 — Sat, 18 Oct 2014 22:19:01 GMT

Hi All,

Thanks for your replies.. sorry that i have missed to provide the details of my requirement more clearly.. Now i have added new data highlighted in red color and the required result in a new sheet.. Could you please help me with the required table in QV.

Please let me know if you have any questions..

Thanks in advance..

Re: Re: Cross table - need help plz..

MarcoWedel — Sat, 18 Oct 2014 22:22:06 GMT

I had a more generic solution in mind, that could also cope with multiple driver intervals per name, vehicle and day:

tabInput:
CrossTable (TimeTxt, Role, 2)
LOAD *
FROM QlikCommunity_Thread_138591.txt (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Left Join (tabInput)
LOAD Distinct
  TimeTxt,
    Time#(TimeTxt) as Time
Resident tabInput;

Left Join (tabInput)
LOAD Name, equipment, Time,
    If(Role='driver' and (Previous(Role)<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), 'time start') as TimeTypeTemp,
    If(Role='driver' and (Previous(Role)<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), RangeSum(Peek(ID),1),Peek(ID)) as ID
Resident tabInput
Order By Name, equipment, Time;

Left Join (tabInput)
LOAD Name, equipment, Time,
    If(Role='driver' and (Previous(Role)<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), 'time end', TimeTypeTemp) as TimeType
Resident tabInput
Order By Name, equipment, Time desc;

tabVehicleDriverUsage:
Generic LOAD ID, Name, equipment, TimeType, Time Resident tabInput;

DROP Table tabInput;

maybe this helps also?

regards

Marco

Re: Re: Re: Cross table - need help plz..

MarcoWedel — Sat, 18 Oct 2014 22:27:06 GMT

forgot the source.

Now attached

regards

Marco

Re: Re: Cross table - need help plz..

MarcoWedel — Sat, 18 Oct 2014 22:40:18 GMT

Hi,

I thought as much that this was your real intention ...

tabInput:
CrossTable (TimeTxt, Role, 2)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/637367-132907/Sample%20data.xlsx] (ooxml, embedded labels, table is Sheet1);

Left Join (tabInput)
LOAD Distinct
  TimeTxt,
    Time(Num#(TimeTxt)) as Time
Resident tabInput;

Left Join (tabInput)
LOAD Name, equipment, Time,
    If(lower(Role)='driver' and (Previous(lower(Role))<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), 'time start') as TimeTypeTemp,
    If(lower(Role)='driver' and (Previous(lower(Role))<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), RangeSum(Peek(ID),1),Peek(ID)) as ID
Resident tabInput
Order By Name, equipment, Time;

Left Join (tabInput)
LOAD Name, equipment, Time,
    If(lower(Role)='driver' and (Previous(lower(Role))<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), 'time end', TimeTypeTemp) as TimeType
Resident tabInput
Order By Name, equipment, Time desc;

tabVehicleDriverUsage:
Generic LOAD ID, Name, equipment, TimeType, Time Resident tabInput;

DROP Table tabInput;

hope this helps

regards

Marco

Re: Re: Re: Cross table - need help plz..

lavanya14 — Sun, 19 Oct 2014 07:22:53 GMT

Hi Macro,

Thanks alot for your quick response. I have couple of questions in the solution plz..

may i know the reason for maintaining the synthetic key.. can we eliminate it..? and what is the reason for the usuage of generic load.. Could you please use the excel attached as your source for your script generation.. when I change the source of the file in your code the numbers are breaking.. Not sure what was the reason.. could you because of the format of the data is causing the numbers to break..

Required table
abc	vehicle1	06:00:00	08:00:00
xyz	vehicle2	08:00:00	09:20:00
abc	vehicle1	09:20:00	10:40:00
xyz	vehicle2	10:00:00	11:20:00

Kindly refer the excel for the source data in sheet1.Please let me know if you have more questions..

Thanks in advance..

Re: Re: Cross table - need help plz..

MarcoWedel — Sun, 19 Oct 2014 07:30:40 GMT

Hi,

I already adapted my solution to your source file

http://community.qlik.com/servlet/JiveServlet/download/637367-132907/Sample%20data.xlsx

in this post:Re: Re: Cross table - need help plz..

Did you change your requirements?

regards

Marco

Re: Re: Re: Cross table - need help plz..

lavanya14 — Sun, 19 Oct 2014 20:43:48 GMT

Hi Macro,

No I have not changed my requirement.. I have taken the file which you gave and changed the source details to my local file.. But the numbers are breaking...

Also could you please clarify me on the clarifications i have asked you in the previous post..

Attaching the file for your reference.. Please let me know if I missed anything and kindly let me know what could be the reason for not getting the results when i change the source file path..

Thanks a lot for your quick responses...

Re: Re: Re: Cross table - need help plz..

lavanya14 — Sun, 19 Oct 2014 20:47:38 GMT

Sorry Marco I have misspelled your name..

Re: Re: Re: Re: Cross table - need help plz..

MarcoWedel — Sun, 19 Oct 2014 23:13:43 GMT

It's OK.

I adapted the script to your latest file and got this result:

QVW and XLS attached.

hope this helps

regards

Marco

Re: Re: Re: Re: Cross table - need help plz..

MarcoWedel — Sun, 19 Oct 2014 23:21:23 GMT

I don't think the synthetic keys in this case are that "evil". There are a number of interesting discussions regarding this topic:

Should We Stop Worrying and Love the Synthetic Key?

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

As for the recombination of the generic tables into one, there is also a fine post that recommends not to do so, although it's possible:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

by Henric Cronström.

hope this helps as well

regards

Marco

Re: Re: Re: Re: Re: Cross table - need help plz..

MarcoWedel — Tue, 21 Oct 2014 11:00:12 GMT

Hi Lavanya,

you asked the following questions via message:

- Could you please let me know the use of the below statement in the solution that you have provided to me..

If(lower(Role)='driver' and (Previous(lower(Role))<>'driver' or Previous(equipment)<>equipment or Previous(Name)<>Name), RangeSum(Peek(ID),1),Peek(ID)) as ID..

Also if i restrict the data to Driver in where clause then ii hope there is no need of the lower(Role) = 'driver'

- DROP Table tabInput; the table has been dropped but still what could be the reason of synthetic key in the data model... could you please tell me the reason.

I used these statements to mark each consecutive driver block with a ID that I could use to associate the start and end times during the generic load.

One way to delete the synthetic keys could be to recombine the generic tables into one as described in the above mentioned threads regarding generic loads and synthetic keys

regards

Marco

Re: Cross table - need help plz..

lavanya14 — Tue, 21 Oct 2014 14:45:04 GMT

Hi Marco,

I managed to get the synthetic key eliminated in the model.. Thanks for your reply....

Re: Cross table - need help plz..

MarcoWedel — Tue, 21 Oct 2014 15:04:07 GMT

You're welcome

if you like, you can mark helpful whatever reply helped you solve your issue.

thanks

regards

Marco