Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Grouping of data in to set of 10 records

Hi Guys,

Can some one help with grouping of data. For the below source data, I need output for two different set of dimensions.

- Trying this for Page break

- Grouping to be done for each ID. This will be the cycling column.

Source:

     

IDEmployeeCityTransactionCode
1RajuChennaiAA1
1RajuChennaiAA1
1RajuChennaiAA1
1RajuChennaiBB2
1RajuChennaiBB2
1RajuHyderabadBB3
1RajuHyderabadBB3
1RajuHyderabadBB4
1RajuHyderabadBB4
1RajuPuneBB5
1RajuPuneBB5
1RahulChennaiAA6
1RahulChennaiAA6
1RahulChennaiAA6
1RahulHyderabadCC6
1RahulHyderabadDD6
1RaviHyderabadEE6
1RaviDelhiEE6
1RaviDelhiEE6
1RaviDelhiEE6
1RaviDelhiEE6
2RahulHyderabadDD6
2RaviHyderabadEE6
2RaviDelhiEE6

Output1:

 

EmployeeTransactionCodeGrouping
RajuAA1Set1
RajuAA1Set1
RajuAA1Set1
RajuBB2Set1
RajuBB2Set1
RajuBB3Set1
RajuBB3Set1
RajuBB4Set1
RajuBB4Set1
RajuBB5Set1
RajuBB5Set2
RahulAA6Set2
RahulAA6Set2
RahulAA6Set2
RahulCC6Set2
RahulDD6Set2
RaviEE6Set2
RaviEE6Set2
RaviEE6Set2
RaviEE6Set3
RaviEE6Set3
RahulDD6Set1
RaviEE6Set2
RaviEE6Set3

 

Output 2:

 

EmployeeCityCodeGrouping
RajuChennai1Set1
RajuChennai1Set1
RajuChennai1Set1
RajuChennai2Set1
RajuChennai2Set1
RajuHyderabad3Set1
RajuHyderabad3Set1
RajuHyderabad4Set1
RajuHyderabad4Set1
RajuPune5Set1
RajuPune5Set2
RahulChennai6Set2
RahulChennai6Set2
RahulChennai6Set2
RahulHyderabad6Set2
RahulHyderabad6Set2
RaviHyderabad6Set2
RaviDelhi6Set2
RaviDelhi6Set2
RaviDelhi6Set3
RaviDelhi6Set3
RahulHyderabad6Set1
RaviHyderabad6Set2
RaviDelhi6Set3

8 Replies
marcus_sommer

For your grouping you could try: 'Set' & ceil(rowno() / 10) as Grouping.

- Marcus

Anonymous
Not applicable
Author

Thanks Marcus. I am actually looking for a piece of code on how can I assign rowno() or recno() for the above input date to getting the provided outputs.

Any suggestions please.

sunny_talwar

Not sure I understand what you are trying to do here. Would you be able to elaborate on your expected outputs?

Anonymous
Not applicable
Author

Outpu1:

For each ID, I need grouping of 10 for target columns Employee, Transaction, Code

Output2:

For each ID, I need grouping of 10 for target columns Employee, City, Code

Note: Target columns are not the Key combinations for grouping. These are just output columns. A single group can have multiple combinations of target columns.

sunny_talwar

Is this something you need in the script or front end? I guess the front end, because this seems like somewhat dynamic?

Anonymous
Not applicable
Author

Need in the script.

Anonymous
Not applicable
Author

very short example

Input:

ID City Employee Transaction Code
1ChennaiRajuAA1
1ChennaiRahulAA6
1DelhiRaviEE6
1HyderabadRajuBB2
1HyderabadRajuBB3
1HyderabadRajuBB4
1HyderabadRahulCC6
1HyderabadRahulDD6
1HyderabadRaviEE6
1LucknowCharanCCC116
1LucknowCharanDDD1166
1MumbaiHariZZ222
1MumbaiHariZZA333
1PuneRajuBB5
2DelhiRaviEE6
2HyderabadRahulDD6
2HyderabadRaviEE6

Output1: Gives us 15 Rows

     

IDEmployeeTransactionCodeGroup
1CharanCCC116Set1
1CharanDDD1166Set1
1HariZZ222Set1
1HariZZA333Set1
1RahulAA6Set1
1RahulCC6Set1
1RahulDD6Set1
1RajuAA1Set1
1RajuBB2Set1
1RajuBB3Set1
1RajuBB4Set2
1RajuBB5Set2
1RaviEE6Set2
2RahulDD6Set1
2RaviEE6Set1

Output2: Give us 16 Rows

     

IDEmployeeCityCodeOutput
1CharanLucknow116Set1
1CharanLucknow1166Set1
1HariMumbai222Set1
1HariMumbai333Set1
1RahulChennai6Set1
1RahulHyderabad6Set1
1RajuChennai1Set1
1RajuHyderabad2Set1
1RajuHyderabad3Set1
1RajuHyderabad4Set1
1RajuPune5Set2
1RaviDelhi6Set2
1RaviHyderabad6Set2
2RahulHyderabad6Set1
2RaviDelhi6Set1
2RaviHyderabad6Set1
Anonymous
Not applicable
Author

Any suggestions with the short example I provided?