Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iswarya
Partner
Partner

Split a single row value into many rows

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. 

Image.JPG

6 Replies
ArnadoSandoval
Specialist II
Specialist II

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

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
iswarya
Partner
Partner
Author

Hi,

I have attached the app for reference.

ArnadoSandoval
Specialist II
Specialist II

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.

RowSplit-01.jpg

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
ArnadoSandoval
Specialist II
Specialist II

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
iswarya
Partner
Partner
Author

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
Specialist II
Specialist II

@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:

Header-Detail-Sel-01.jpg

and

Header-Detail-Sel-02.jpg

The new version of the application is attached.

Best regards,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.