Convert Rows to Columns based on Condition in Script

[Sai33]

Hello All,

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

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

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

ID	Place	Entry Timestamp	Exit Timestamp	Event Number
0B81432	SPAP		17.01.2019 12:45	0
0B81432	CHSP	19.01.2019 08:35	24.01.2019 13:36	1
0B81432	SPAP	24.01.2019 13:36		2

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

Thanks

Sai

[Gui_Approbato]

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 · Accepted solution]

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.

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

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.