topic Re: Converting rows in table into columns challenge in QlikView

Converting rows in table into columns challenge

Mon, 01 Jun 2015 15:29:37 GMT

Hi All,

I have a column in my load script that shows OrderID, TrackingItem, and DateEntered that comes from a database table (not Excel).

An example for two orders would be:

OrderId, TrackingItem, DateEntered

1 Open_Date 5/1/2015

1 Assigned_Date 5/2/2015

1 Completed_Date 5/3/2015

2 Open_Date 5/4/2015

2 Assign_Date 5/5/2015

2 Complete_Date 5/6/2015

So I would like to convert the rows to columns as follows:

OrderID Open_Date Assigned_Date Completed_Date

1 5/1/2015 5/2/2015 5/3/2015

2 5/4/2015 5/5/2015 5/6/2015

But the best I can do is:

OrderID Open_Date Assigned_Date Completed_Date

1 5/1/2015 - -

1 - 5/2/2015

1 5/3//2015

etc.

Any suggestions to get the dates into one row.

Thanks.

Don

Re: Converting rows in table into columns challenge

sunny_talwar — Mon, 01 Jun 2015 15:31:50 GMT

You will find this Blog to be useful. Check it out: The Generic Load

Best,

Sunny

Re: Converting rows in table into columns challenge

maxgro — Mon, 01 Jun 2015 15:39:25 GMT

test:

load * inline [

OrderId, TrackingItem, DateEntered

1 , Open_Date , 5/1/2015

1 , Assigned_Date, 5/2/2015

1 , Completed_Date, 5/3/2015

2 , Open_Date , 5/4/2015

2 , Assigned_Date , 5/5/2015

2 , Completed_Date , 5/6/2015

];

final:

load OrderId, DateEntered as Open_Date Resident test where TrackingItem = 'Open_Date';

join (final) load OrderId, DateEntered as Assigned_Date Resident test where TrackingItem = 'Assigned_Date';

join (final) load OrderId, DateEntered as Completed_Date Resident test where TrackingItem = 'Completed_Date';

DROP Table test;

Re: Converting rows in table into columns challenge

petter — Mon, 01 Jun 2015 15:40:01 GMT

You can use Max(Open_Date), Max(Assigned_Date) and Max(Completed_Date) then the three lines for every OrderID will turn into one.

Re: Converting rows in table into columns challenge

sunny_talwar — Mon, 01 Jun 2015 15:41:50 GMT

Try this script:

Table:

GENERIC LOAD * Inline [

OrderId, TrackingItem, DateEntered

1, Open_Date, 5/1/2015

1, Assigned_Date, 5/2/2015

1, Completed_Date, 5/3/2015

2, Open_Date, 5/4/2015

2, Assigned_Date, 5/5/2015

2, Completed_Date, 5/6/2015

];

FinalTable:

NoConcatenate

LOAD *

Resident Table.Open_Date;

Join(FinalTable)

LOAD *

Resident Table.Assigned_Date;

Join(FinalTable)

LOAD *

Resident Table.Completed_Date;

DROP Tables Table.Open_Date, Table.Assigned_Date, Table.Completed_Date;

Output:

Re: Converting rows in table into columns challenge

Mon, 01 Jun 2015 15:42:22 GMT

Thanks all for your quick response. Testing now.

Re: Converting rows in table into columns challenge

Mon, 01 Jun 2015 15:58:13 GMT

Massimo, thanks again. You have helped me in the past.

Re: Converting rows in table into columns challenge

Mon, 01 Jun 2015 15:58:47 GMT

I will also give you're response a very close look.

Re: Converting rows in table into columns challenge

Mon, 01 Jun 2015 15:59:57 GMT

Sunny, Thank you for your response. I'm still working with it. Very helpful.

Re: Converting rows in table into columns challenge

sunny_talwar — Mon, 01 Jun 2015 16:01:26 GMT

No problem

Re: Converting rows in table into columns challenge

MarcoWedel — Mon, 01 Jun 2015 17:49:08 GMT

Hi,

another solution could be to simply add the 'generic' prefix to your load statement:

Generic

LOAD * INLINE [

OrderId, TrackingItem, DateEntered

1, Open_Date, 5/1/2015

1, Assigned_Date, 5/2/2015

1, Completed_Date, 5/3/2015

2, Open_Date, 5/4/2015

2, Assigned_Date, 5/5/2015

2, Completed_Date, 5/6/2015

];

hope this helps

regards

Marco

Re: Converting rows in table into columns challenge

Mon, 01 Jun 2015 18:25:35 GMT

Don Saluga

Senior Business Intelligence Analyst, Valuations and Vision Global Solutions

ServiceLink

A Black Knight Financial Services Company

Re: Converting rows in table into columns challenge

Mon, 01 Jun 2015 18:43:19 GMT

Marco and Sunny,

I may try to use the Generic load. I may have possibly 53 different types of dates. This may handle the job better. I found this useful code from Rob Wunderlich on Generic Loads.

Use cases for Generic Load | Qlikview Cookbook

Re: Converting rows in table into columns challenge

MarcoWedel — Mon, 01 Jun 2015 19:37:47 GMT

Yes, you are right. rwunderlich‌ is a very good source indeed.

As far as I know, he has since improved his code to recombine generic tables:

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'Attributes.*') THEN
  LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

hope this helps

regards

Marco

Re: Converting rows in table into columns challenge

rwunderlich — Mon, 01 Jun 2015 20:56:38 GMT

Marco, thanks for the reminder that the post could use some updating I've updated the post with the new code.

Also, if you use QVC (http://qlikviewcomponents.org), you can combine the tables with the single statement:

CALL Qvc.JoinGenericTables('OrdersTable', 'Flags');

-Rob

http://masterssummit.com

http://robwunderlich.com