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: 
Sai33
Partner - Creator
Partner - Creator

Convert Rows to Columns based on Condition in Script

Hello All,

I've the following data and based on a condition i would like to Split one row into two.

IDPlaceTypeEvent TimestampEvent Number
0B81432SPAP217.01.2019 12:450
0B81432CHSP119.01.2019 08:351
0B81432CHSP224.01.2019 13:361
0B81432SPAP124.01.2019 13:362

 

From the above Input data, i would like to Split Event Timestamp into two based on Type(If type=2 - Exit and type=1-Entry) . So, my Expected Output data would be as below

IDPlaceEntry TimestampExit TimestampEvent Number
0B81432SPAP 17.01.2019 12:450
0B81432CHSP19.01.2019 08:3524.01.2019 13:361
0B81432SPAP24.01.2019 13:36 2

 

Please give a hint if my question is not clear or if any further inputs are required.

 

Thanks

Sai

Labels (1)
1 Solution

Accepted Solutions
Saravanan_Desingh

One solution is.

tab1:
LOAD * INLINE [
    ID, Place, Type, Event Timestamp, Event Number
    0B81432, SPAP, 2, 17.01.2019 12:45, 0
    0B81432, CHSP, 1, 19.01.2019 08:35, 1
    0B81432, CHSP, 2, 24.01.2019 13:36, 1
    0B81432, SPAP, 1, 24.01.2019 13:36, 2
];

Gen:
Generic
LOAD ID, Place, [Event Number],
	 If(Type=1, 'Entry ', 'Exit ')&'Timestamp', [Event Timestamp]
Resident tab1;

Drop Table tab1;

View solution in original post

7 Replies
Gui_Approbato
Creator III
Creator III

Hello,

You just need to make a small change in your load script.

This would be like:

Load

 ID,

Place,

Type,

//     "Event Timestamp",

if( Type='1' , "Event Timestamp") as "Entry Timestamp",

if( Type='2' , "Event Timestamp") as "Exit Timestamp",

"Event Number"  from [Source];

 

Hope it helps.

Best

Kushal_Chawda

try below

Data:
LOAD
    ID,
    Place,
     "Event Timestamp" as [Entry Timestamp],
    "Event Number"
FROM [lib://Web]
(html, utf8, embedded labels, table is @1)
where "Type"=1;

Join(Data)
LOAD
    ID,
    Place,
     "Event Timestamp" as [Exit Timestamp],
     "Event Number"   
FROM [lib://Web]
(html, utf8, embedded labels, table is @1)
where "Type"=2;
Saravanan_Desingh

One solution is.

tab1:
LOAD * INLINE [
    ID, Place, Type, Event Timestamp, Event Number
    0B81432, SPAP, 2, 17.01.2019 12:45, 0
    0B81432, CHSP, 1, 19.01.2019 08:35, 1
    0B81432, CHSP, 2, 24.01.2019 13:36, 1
    0B81432, SPAP, 1, 24.01.2019 13:36, 2
];

Gen:
Generic
LOAD ID, Place, [Event Number],
	 If(Type=1, 'Entry ', 'Exit ')&'Timestamp', [Event Timestamp]
Resident tab1;

Drop Table tab1;
Saravanan_Desingh

Output.

commQV58.PNG

Kushal_Chawda

@Sai33  Be careful when you use generic load approach. It will create synthetic keys between the tables and eventually will impact the performance of app. So  join approach will be better.

Sai33
Partner - Creator
Partner - Creator
Author

Hi @Kushal_Chawda 

Thank you for your tips!

Yes, you're correct the Generic Load is creating synthetic keys but it somehow serves my purpose. Strangely the join approach is not working in my case. Do you have any other pointers on how to implement join.

 

Regards

Sai

Kushal_Chawda

@Sai33  I already suggested join option but as you said it is not working in your case then without looking at your actual data I can't say what was wrong.