convert rows to columns based on ID and Type

[#1]

Hi

I have the SQL out put in the below format. Need to convert them into the rows for each id, Based on the type the column name changes and store them in a QVD.

Id, First date , Last date, type

1, 01/Apr/2014, 04/Apr/2014, S

1,01/May/2014, 05/May/2014,B

1,10/May/2014,05/Jun/2014,C

2, 02/Apr/2014, 04/Apr/2014, S

2,03/May/2014, 05/May/2014,B

2,15/May/2014,05/Jun/2014,C

3, 01/Apr/2014, 04/Apr/2014, S

3,01/May/2014, 05/May/2014,B

3,10/May/2014,05/Jun/2014,C

....

ID	S_first_date	S_last_date	B_first_date	B_last_date	C_first_date	C_last_date
1	1-Apr-14	4-Apr-14	1-May-14	5-May-14	10-May-14	5-Jun-14
1	2-Apr-14	4-Apr-14	3-May-14	5-May-14	15-May-14	5-Jun-14
3	1-Apr-14	4-Apr-14	1-May-14	5-May-14	10-May-14	5-Jun-14

thanks..

JJ

[swuehl]

Converted:

LOAD Id,

      [First date] as [S_first_date],

      [Last date] as [S_last_date]

RESIDENT Yourtable

WHERE type = 'S';

LEFT JOIN (Converted)

LOAD Id,

      [First date] as [B_first_date],

      [Last date] as [B_last_date]

RESIDENT Yourtable

WHERE type = 'B';

LEFT JOIN (Converted)

LOAD Id,

      [First date] as [C_first_date],

      [Last date] as [C_last_date]

RESIDENT Yourtable

WHERE type = 'C';

STORE Converted INTO Converted.qvd (qvd);

[ramoncova06]

Another option

tmp:
load * inline [
Id, First date , Last date, type
1, 01/Apr/2014, 04/Apr/2014, S
1,01/May/2014, 05/May/2014,B
1,10/May/2014,05/Jun/2014,C
2, 02/Apr/2014, 04/Apr/2014, S
2,03/May/2014, 05/May/2014,B
2,15/May/2014,05/Jun/2014,C
3, 01/Apr/2014, 04/Apr/2014, S
3,01/May/2014, 05/May/2014,B
3,10/May/2014,05/Jun/2014,C
](delimiter is ',');

  set vLoadType = '';


  for TypeCounter = 1 to FieldValueCount('type')


  let vType = FieldValue('type',$(TypeCounter));



  Temp:
  $(vLoadType)
  load 
      Id,
  [First date] as [$(vType)_First date],
  [Last date] as [$(vType)_Last date]
  resident tmp
  where type = '$(vType)' ;



  set vLoadType = 'left join';


  next



  drop table tmp;






STORE Temp into temp.qvd(qvd);

[petter]

Yet another way of getting a pivot(ed) table in a load script - which is an adaption of a tried and true method from SQL - and any other database/query language that can do aggregates:

JOIN (DataInput)

PIVOT_matrix:
LOAD * INLINE [
type, S_, B_, C_
S, 1,0,0
B, 0,1,0
C, 0,0,1
];

Data:
LOAD
  Id,
  Date( Sum( [First date] * S_ ) ) AS S_first_date,
  Date( Sum( [Last date]  * S_ ) ) AS S_last_date,
  Date( Sum( [First date] * B_ ) ) AS B_first_date,
  Date( Sum( [Last date]  * B_ ) ) AS B_last_date,
  Date( Sum( [First date] * C_ ) ) AS C_first_date,
  Date( Sum( [Last date]  * C_ ) ) AS C_last_date
RESIDENT  DataInput
GROUP BY  Id;


STORE Data INTO Converted.QVD (QVD);

[petter]

A fourth variant which I am pretty sure is more efficient than my first suggestion - by not using joins and not relying on a resident table - just use preceding loads:

Data:
LOAD
  Id,
  Date( Sum( [First date] * S_ ) ) AS S_first_date,
  Date( Sum( [Last date]  * S_ ) ) AS S_last_date,
  Date( Sum( [First date] * B_ ) ) AS B_first_date,
  Date( Sum( [Last date]  * B_ ) ) AS B_last_date,
  Date( Sum( [First date] * C_ ) ) AS C_first_date,
  Date( Sum( [Last date]  * C_ ) ) AS C_last_date
GROUP BY Id;
LOAD *, -(type='S') AS S_, -(type='B') AS B_, -(type='C') AS C_;
SQL
     SELECT Id,[First date],[Last date],type FROM SourceTable;

STORE Data INTO Converted.QVD (QVD);

[petter]

Sorry - I overcomplicate things a little bit - this is the simplest solution I can come up with:

Data:
LOAD
  Id,
  Date( Sum( If( type='S' , [First date]) )) AS S_first_date,
  Date( Sum( If( type='S' , [Last date])  )) AS S_last_date,
  Date( Sum( If( type='B' , [First date]) )) AS B_first_date,
  Date( Sum( If( type='B' , [Last date])  )) AS B_last_date,
  Date( Sum( If( type='C' , [First date]) )) AS C_first_date,
  Date( Sum( If( type='C' , [Last date])  )) AS C_last_date
GROUP BY Id;
SQL SELECT Id,[First date],[Last date],type FROM SourceTable;

[petter]

I think using Sum() might not be the best aggregation function although it works perfectly well for this case and consistent data. LastValue() or Concat() is probably much better to get a more fail-proof result even with multiple values returned.

[#8]

Hi jgonweb,

Try like this:

Main: 

LOAD 

  Id, 

  Date( Sum( If( type='S' , [First date]) )) AS S_first_date, 

  Date( Sum( If( type='S' , [Last date])  )) AS S_last_date, 

  Date( Sum( If( type='B' , [First date]) )) AS B_first_date, 

  Date( Sum( If( type='B' , [Last date])  )) AS B_last_date, 

  Date( Sum( If( type='C' , [First date]) )) AS C_first_date, 

  Date( Sum( If( type='C' , [Last date])  )) AS C_last_date 

GROUP BY Id; 

SQL SELECT Id,[First date],[Last date],type FROM Table; 

Ramya.

[jagan]

Hi,

Try like this

Temp:

LOAD

Id,

Date(Date#([First date], 'DD/MMM/YYYY') , 'DD/MMM/YYYY')AS FirstDate,

Date(Date#([Last date], 'DD/MMM/YYYY'), 'DD/MMM/YYYY') AS LastDate,

type

INLINE [

Id, First date , Last date, type

1, 01/Apr/2014, 04/Apr/2014, S

1,01/May/2014, 05/May/2014,B

1,10/May/2014,05/Jun/2014,C

2, 02/Apr/2014, 04/Apr/2014, S

2,03/May/2014, 05/May/2014,B

2,15/May/2014,05/Jun/2014,C

3, 01/Apr/2014, 04/Apr/2014, S

3,01/May/2014, 05/May/2014,B

3,10/May/2014,05/Jun/2014,C];

Data:

LOAD

Id,

FirstDate AS S_FirstDate,

LastDate AS S_LastDate

RESIDENT Temp

WHERE type = 'S';

Left Join(Data)

LOAD

Id,

FirstDate AS B_FirstDate,

LastDate AS B_LastDate

RESIDENT Temp

WHERE type = 'B';

Left Join(Data)

LOAD

Id,

FirstDate AS C_FirstDate,

LastDate AS C_LastDate

RESIDENT Temp

WHERE type = 'C';

DROP TABLE Temp;

Regards,

jagan.

[jagan]

OR

Try it in a single statement like below

Temp:

LOAD

Id,

Date(Date#([First date], 'DD/MMM/YYYY') , 'DD/MMM/YYYY')AS FirstDate,

Date(Date#([Last date], 'DD/MMM/YYYY'), 'DD/MMM/YYYY') AS LastDate,

type

INLINE [

Id, First date , Last date, type

1, 01/Apr/2014, 04/Apr/2014, S

1,01/May/2014, 05/May/2014,B

1,10/May/2014,05/Jun/2014,C

2, 02/Apr/2014, 04/Apr/2014, S

2,03/May/2014, 05/May/2014,B

2,15/May/2014,05/Jun/2014,C

3, 01/Apr/2014, 04/Apr/2014, S

3,01/May/2014, 05/May/2014,B

3,10/May/2014,05/Jun/2014,C];

Data:

LOAD

  Id,

  Date( Sum( If( type='S' , FirstDate) )) AS S_first_date,

  Date( Sum( If( type='S' , LastDate)  )) AS S_last_date,

  Date( Sum( If( type='B' , FirstDate) )) AS B_first_date,

  Date( Sum( If( type='B' , LastDate)  )) AS B_last_date,

  Date( Sum( If( type='C' , FirstDate) )) AS C_first_date,

  Date( Sum( If( type='C' , LastDate)  )) AS C_last_date

Resident Temp

GROUP BY Id;

DROP TABLE Temp;