Custom Data Issue

Mon, 11 Jun 2012 18:12:37 GMT

A	B
1009	1
KSN	2
USA	3
2009	4
NNN	5
UK	6
5077	7
LLT	8
UK	9

Table 1 (Input)

Above table is the sample data.I want the above table to cross table in the below format.Any help...

Thanks

ID	Name	Country
1009	KSN	USA
2009	NNN	UK
5077	LLT	UK

Re: Custom Data Issue

erichshiino — Mon, 11 Jun 2012 19:52:33 GMT

Hi,

I segmented your original table into three and them joined them back to create column.

temp:
LOAD * INLINE [
    A, B
    109, 1
    KSN, 2
    USA, 3
    2009, 4
    NNN, 5
    UK, 6
    5077, 7
    LLT, 8
    UK, 9
];
Table1:
Load A, 
          B, 
          Floor((B-1)  /  3)  AS C //EACH GROUP OF 3 ROWS WILL HAVE THE SAME C
RESIDENT temp; 
drop table temp; 
Final:
Load A AS ID, C as joinField resident Table1 
where mod(B-1,3)  =  0 //GETS THE FIRST LINE OF EACH GROUP
;
left join(Final)
Load A as Name, C as joinField resident Table1 where  mod(B-1,3)  =  1 
 //GETS THE SECOND LINE OF EACH GROUP
 ; 
left join(Final)
Load A as Country, C as joinField resident Table1 where  mod(B-1,3)  =  2 
 //GETS THE THRID LINE OF EACH GROUP
 ; 
drop table Table1 ; 
//You may drop the Joinfield
//drop field joinField ;

Hope it helps,

Erich

Custom Data Issue

Mon, 11 Jun 2012 20:36:02 GMT

Hi Erich,

Thanks ..It works perfect.

Regards

topic Re: Custom Data Issue in QlikView

Custom Data Issue

Re: Custom Data Issue

Custom Data Issue