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_keyIDTypeType 2DivisionPillarCost CenterCompany5/31/202221Transfer OutExitsX Division 5/31/202221Transfer INAddsY Division 5/31/202221Transfer OutExits A 5/31/202221Transfer INAdds B 5/31/202221Transfer OutExits C 5/31/202221Transfer INAdds D 5/31/202221Transfer OutExits E5/31/202221Transfer INAdds F Expected Output is : DateIDTypeType 2DivisionPillarCost CenterCompany5/31/202221Transfer OutExitsX DivisionACE5/31/202221Transfer INAddsY DivisionBDF Thanks, Kuldeep Join Error.yxmd Book1.xlsx

NO Cartesian join required

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 :

Expected Output is :

Thanks,

Kuldeep

Datasets

Join

Expression

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

Regards,

Ben

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

@Kuldeepmathur One way of doing this with the Summarize tool

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 :

Output :

Thanks,

Kuldeep

Thanks Everybody for the support !