topic Re: convert rows to columns based on ID and Type in QlikView

convert rows to columns based on ID and Type

Sun, 07 Jun 2015 03:44:34 GMT

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

Re: convert rows to columns based on ID and Type

swuehl — Thu, 25 Jun 2015 22:34:38 GMT

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);

Re: convert rows to columns based on ID and Type

ramoncova06 — Thu, 25 Jun 2015 23:11:12 GMT

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);

Re: convert rows to columns based on ID and Type

petter — Fri, 26 Jun 2015 05:01:51 GMT

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);

Re: convert rows to columns based on ID and Type

petter — Fri, 26 Jun 2015 05:30:31 GMT

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);

Re: convert rows to columns based on ID and Type

petter — Fri, 26 Jun 2015 05:53:29 GMT

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;

Re: convert rows to columns based on ID and Type

petter — Fri, 26 Jun 2015 06:06:28 GMT

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.

Re: convert rows to columns based on ID and Type

Fri, 26 Jun 2015 06:07:09 GMT

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.

Re: convert rows to columns based on ID and Type

jagan — Fri, 26 Jun 2015 06:27:53 GMT

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.

Re: convert rows to columns based on ID and Type

jagan — Fri, 26 Jun 2015 06:30:08 GMT

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;

Re: convert rows to columns based on ID and Type

sasiparupudi1 — Fri, 26 Jun 2015 10:47:35 GMT

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];

Data:

LOAD

Id ,

Date(Sum(If(type='S',Date#([First date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS S_first_date,

Date(Sum(If(type='S',Date#([Last date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS S_last_date,

Date(Sum(If(type='B',Date#([First date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS B_first_date,

Date(Sum(If(type='B',Date#([Last date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS B_last_date,

Date(Sum(If(type='C',Date#([First date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS C_first_date,

Date(Sum(If(type='C',Date#([Last date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS C_last_date

Resident X

Group By Id ;

STORE Data into Data.qvd(qvd);

DROP TABLE X;