NO Cartesian join required

[Kuldeepmathur]

Hi,

I am trying to create a movement dataset where I am trying to record the movement of Ids from Last month to this month

My input file looks like this :

rpt_dt_key	ID	Type	Type 2	Division	Pillar	Cost Center	Company
5/31/2022	21	Transfer Out	Exits	X Division
5/31/2022	21	Transfer IN	Adds	Y Division
5/31/2022	21	Transfer Out	Exits		A
5/31/2022	21	Transfer IN	Adds		B
5/31/2022	21	Transfer Out	Exits			C
5/31/2022	21	Transfer IN	Adds			D
5/31/2022	21	Transfer Out	Exits				E
5/31/2022	21	Transfer IN	Adds				F

Expected Output is :

Date	ID	Type	Type 2	Division	Pillar	Cost Center	Company
5/31/2022	21	Transfer Out	Exits	X Division	A	C	E
5/31/2022	21	Transfer IN	Adds	Y Division	B	D	F

Thanks,

Kuldeep

[grazitti_sapna]

Hi @Kuldeepmathur , Here is the workflow. You can achieve it by using multi join. Please let me know if it was helpful.

[binuacs]

@Kuldeepmathur One way of doing this with the Summarize tool

[Kuldeepmathur]

Thanks for the quick revert  I really appreciate your support.

I just added some more Ids in the data and it's actually breaking because of the date change 

Input : 

Date	ID	Type	Type 2	Division	Pillar	Cost Center	Company
5/31/2022	21	Transfer Out	Exits	X Division
5/31/2022	21	Transfer IN	Adds	Y Division
5/31/2022	21	Transfer Out	Exits		A
5/31/2022	21	Transfer IN	Adds		B
5/31/2022	21	Transfer Out	Exits			C
5/31/2022	21	Transfer IN	Adds			D
5/31/2022	21	Transfer Out	Exits				E
5/31/2022	21	Transfer IN	Adds				F
6/31/2022	22	Transfer Out	Exits	G Division
6/31/2022	22	Transfer IN	Adds	H Division
6/31/2022	22	Transfer Out	Exits				M
6/31/2022	22	Transfer IN	Adds				N

Output :

Thanks,

Kuldeep

[Ben_H · Accepted solution]

Hi @Kuldeepmathur,

Here's an alternative method using transpose and crosstab that should work for you.

Regards,

Ben

[Kuldeepmathur]

Thanks Everybody for the support !