Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@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;
@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.
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.
@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;