Converting Similar Records to Rows - Pivoting Data within the script

[andrewwilliamp]

Hello, I am attempting to take similar records and convert the data into rows to be further filtered on and joined with other tables, so I believe this can not be accomplished in the front-end.

Currently my data looks like this: 

TICKETNUMBER	MMPLANT
123456	FR42
123456	FR01
123456	FR04
654321	FR42
654321	FR43

I need the data to look like this:

TICKETNUMBER	PRIMARYPLANT	PLANT01	PLANT02
123456	FR42	FR01	FR04
654321	FR42	FR43

Some of the 'TICKETNUMBER' records could have up to 33 'MMPLANT' records assocaiated with them...so having fields that go up to 'PLANT32' would be necessary at times. Anyone have ideas on how this can be accomplished? Apologies if this is posted in the incorrect place, I am quite new to this! 

Thanks. 

[Kushal_Chawda]

@andrewwilliamp  how do you identify primary plant? Seems like you are considering first record for each ticket no but ideally there should have some other column like date on which applying sorting could identify primary plant.

[andrewwilliamp]

Thanks for the response Kushal, Primary plant will be purely based on the first record associated with that specific ticket number so there is no date associated with it, yet. I could apply a count field that numbers the records based on the ticket number or something similar if needed?

This table will be joined with another table that is already formatted correctly with various fields for each plant - that table does have dates associated with the ticket numbers. 

[Kushal_Chawda · Accepted solution]

@andrewwilliamp  try below

Data:
Load TICKETNUMBER,
     MMPLANT,
     if(TICKETNUMBER=Previous(TICKETNUMBER), 'PLANT '& rangesum(keepchar(Peek('PLANT_NO'),'0123456789'),1),null()) as PLANT_NO
FROM Table;

left Join(Data)
Load TICKETNUMBER,
     FirstValue(MMPLANT) as PRIMARYPLANT
Resident Data
Group by TICKETNUMBER;

plants_trasformed:
Generic Load TICKETNUMBER,
             PLANT_NO,
             MMPLANT
Resident Data
where not IsNull(PLANT_NO);

For i=0 to NoOfTables()

let vTableName = TableName('$(i)');

if WildMatch('$(vTableName)','*plants_trasformed.*') then

Tables:
Load '$(vTableName)' as Tables
AutoGenerate 1;

EndIf
 
Next 

Plants_final:
Load FieldValue('TICKETNUMBER',RecNo()) as TICKETNUMBER
AutoGenerate FieldValueCount('TICKETNUMBER');

for Each j in FieldValueList('Tables')

Left Join (Plants_final) Load * Resident [$(j)];

Drop Table [$(j)];

next

Left Join(Plants_final)
Load Distinct TICKETNUMBER,
              PRIMARYPLANT
Resident Data;

Drop Tables Tables,Data;