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 

[sunny_talwar]

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

Best,

Sunny

[maxgro · Accepted solution]

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;

[petter]

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

[sunny_talwar]

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:

[#6]

Thanks all for your quick response.  Testing now.

[#7]

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

[#8]

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

[#9]

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

[sunny_talwar]

No problem