Cross table - need help plz..

[lavanya14]

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.

[MK_QSL]

Here you go !!!

[MarcoWedel]

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

[Anonymous]

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

[lavanya14]

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

[MarcoWedel]

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

[MarcoWedel]

forgot the source.

Now attached

regards

Marco

[MarcoWedel]

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

[lavanya14]

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

[MarcoWedel]

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