Converting rows in table into columns challenge

[#1]

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 

[MarcoWedel]

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

[#3]

Don Saluga

Senior Business Intelligence Analyst, Valuations and Vision Global Solutions

ServiceLink

A Black Knight Financial Services Company

[#4]

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

[MarcoWedel]

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

[rwunderlich]

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