Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I want to divide the column data into 2 columns with respect to row field. I have data as below
If data has to group by id and if the A field data belongs to Date 1/31/2023 then it has to display in Column A1 and if the data belongs to date 12/31/2022 then it has to display in Column A2
Id | Date | A |
1 | 1/31/2023 | X |
1 | 12/31/2022 | Y |
2 | 1/31/2023 | X |
2 | 12/31/2022 | Y |
I need the output like below
Id | A1 | A2 |
1 | X | Y |
2 | X | Y |
Thanks in advance
you can use this code i tested and it is working very well
Table:
Load * Inline
[Id , Date , A
1 ,1/31/2023, X
1, 21/31/2022 , Y
2, 1/31/2023 ,X
2 ,21/31/2022, Y];
Map_Date:
Mapping Load
[Date],
Rowno() as Index
resident Table;
Table_Temp:
Load
*,
'A'& Applymap('Map_Date',[Date]) as ID_A
resident Table;
Drop table Table;
And Create a Pivot table with Id as Ligne and Id_A as a column and A as a mesure
i found this result
Is the date supposed to influence the resulting order or is it just the order of appearance that decides?
Please also provide some more examples/variations to clarify.
I need to sort as per the latest date first and need the solution in load script editor
Shortest solution I can think of would be:
table1:
Generic
LOAD Id,
'A'&AutoNumber(RecNo(),Id),
A
Inline [
Id, Date, A
1, 1/31/2023, X
1, 12/31/2022, Y
2, 1/31/2023, X
2, 12/31/2022, Y
];
(for data already ordered by date like in your example)