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.

[lavanya14]

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

[lavanya14]

Sorry Marco I have misspelled your name..

[MarcoWedel · Accepted solution]

It's OK.

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

QVW and XLS attached.

hope this helps

regards

Marco

[MarcoWedel]

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

[MarcoWedel]

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

[lavanya14]

Hi Marco,

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

[MarcoWedel]

You're welcome

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

thanks

regards

Marco