Split a single row value into many rows

[iswarya]

Hi All,

I want to split a single row's value into multiple rows.

E.g: For location 11 & item ABC, the qty is 2 so its corresponding fields(NO,DATE,COMMENTS) should be split into next two rows. If the qty is 5 then 5 corresponding rows to be added. 

[ArnadoSandoval]

Hi @iswarya 

Can you share or explain your source data? It seems the screenshot is for the result you want to get.

Regards,

Arnaldo Sandoval

[iswarya]

Hi,

I have attached the app for reference.

[ArnadoSandoval]

Hi @iswarya 

You are dealing with a Header-Detail model, my proposed solution take these actions:

• TABLE1_FINAL are your header records; Type '1H'
• TABLE2_FINAL are your detail records.; Type '1D'
• We created the Result table by concatenating your TABLE2_FINAL with TABLE1_FINAL (just its key column, its remaining columns were set to null)
• We order the Result table by KEY and Type adding the record number (named Record).
• We renamed Result back to TABLE2_FINAL,  featuring the column Type with values '1H', '1D'

This screenshot shows the result you want.

Comments:

• All the columns on this table have a condition, based on the record Type, e.g. 1H or 1D.
• The last column is Record, it has conditions to 'hide' it from view.
• The data is sorted by Record + LOCATION + ITEM ... and so on.

I attached the solution.

Hope this helps,

[ArnadoSandoval]

Hi @iswarya 

It seems you deleted your reply, anyhow, you can swap the Type tag, replace '1H' with '1D' and '2D' with '1H', then, at the user interface you should change the conditions, checking for '1D' instead of '1H'

=if(Type = '1D', [Detail_Column_Name], ' ') 
// for the LOCATION, ITEM and QTY columns
and
=If(Type = '1D', ' ', [Header_Column_Name])
// for the NO and COMMENTS columns

I tried different visualizations, like Straight Table; Pivot Table and P&L Pivot but the standard Table does the job.

Do You think my proposed solution resolve your problem?

Regards,

[iswarya]

Hi @ArnadoSandoval 

It's half resolved. But is it possible to bring the location and Item name against the no and comments column? Because when I make a selection for location or Item, the 'NO' and 'COMMENTS' field is not displayed.

[ArnadoSandoval]

@iswarya 

Yes you can, actually this is a summary of the changes:

• The Table TABLE2_FINAL now features the columns: ITEM_HD and LOCATION_HD; this avoid a synthetic key with the columns with same name on the TABLE1_FINAL
• We are no longer dropping the columns ITEM_HD and LOCATION_HD from the TABLE2_FINAL
• Added the variable vOptions = 1;  This will be used by a dropdown selector: Header-Details or All
• The conditions for the columns LOCATION and ITEM now include the variable vOptions.
• The LOCATION selector is based on the LOCATION_HD, I did not try the LOCATION column, it should work the same.

These are the screenshots:

and

The new version of the application is attached.

Best regards,