Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eromiyasimon
Creator II
Creator II

Converting Few row into column but not the all

Hi All,

I need to convert few row into column in a table but not all the rows.

I will clear by the below example

From 

DateTypeFlag
01-Jan-18AeroY
02-Jan-18AeroY
03-Jan-18AeroY
04-Jan-18AeroY
05-Jan-18AeroY
06-Jan-18BrokeY
07-Jan-18BrokeN
08-Jan-18BrokeN
09-Jan-18ClapN
10-Jan-18ClapN
11-Jan-18ClapN
12-Jan-18ClapN

to

 

DateAeroBrokeClap
01-Jan-18Y--
02-Jan-18Y--
03-Jan-18Y--
04-Jan-18Y--
05-Jan-18Y--
06-Jan-18-Y-
07-Jan-18-N-
08-Jan-18-N-
09-Jan-18--N
10-Jan-18--N
11-Jan-18--N
12-Jan-18--N

 

Labels (1)
2 Replies
tincholiver
Creator III
Creator III

Try this:

 

Table:
load * Inline
[
Date, Type, Flag
01-Jan-18, Aero, Y
02-Jan-18, Aero, Y
03-Jan-18, Aero, Y
04-Jan-18, Aero, Y
05-Jan-18, Aero, Y
06-Jan-18, Broke, Y
07-Jan-18, Broke, N
08-Jan-18, Broke, N
09-Jan-18, Clap, N
10-Jan-18, Clap, N
11-Jan-18, Clap, N
12-Jan-18, Clap, N
];

NoConcatenate

Aero:
LOAD
Date,
Flag as Aero
Resident Table Where Type='Aero';
Concatenate
Broke:
LOAD
Date,
Flag as Broke
Resident Table Where Type='Broke';
Concatenate
Clap:
LOAD
Date,
Flag as Clap
Resident Table Where Type='Clap';
DROP Table Table;

 

let me know if it helps you

raman_rastogi
Partner - Creator III
Partner - Creator III

You can use Generic load also.

Try this 

Data:
Generic Load *;
load * Inline
[
Date, Type, Flag
01-Jan-18, Aero, Y
02-Jan-18, Aero, Y
03-Jan-18, Aero, Y
04-Jan-18, Aero, Y
05-Jan-18, Aero, Y
06-Jan-18, Broke, Y
07-Jan-18, Broke, N
08-Jan-18, Broke, N
09-Jan-18, Clap, N
10-Jan-18, Clap, N
11-Jan-18, Clap, N
12-Jan-18, Clap, N
];
   Set vListOfTables = ;
   For vTableNo = 0 to NoOfTables()
      Let vTableName = TableName($(vTableNo)) ;
      If Subfield(vTableName,'.',1)='Data' Then
         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
      End If
   Next vTableNo
   CombinedTable:
   load * Inline
[Date];
   For each vTableName in $(vListOfTables)
      Join (CombinedTable) Load * Resident [$(vTableName)];
      Drop Table [$(vTableName)];
   Next vTableName

 

Regards

Raman