topic Re: How to Group By (Empty Values) in Connectivity & Data Prep

How to Group By (Empty Values)

virus190 — Tue, 07 Sep 2021 15:18:09 GMT

Hi,

i cant solve my problem, maybe you can. It looks easy, but i have no idea.

So lets say, i have 2 tables:

1. Data:

2. ExtraData:

Now i want to create a table that looks like that:

I tried to use Masterelemts (for exampe) if(Group='Car',Brand), but then i get empty values like that:

What is the right way to solve this? With the data load editor? I tried but couldn't

Re: How to Group By (Empty Values)

stevejoyce — Tue, 07 Sep 2021 15:37:45 GMT

You can use generic load for this that will unpivot your group and you can have brand as your cell value

https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470

If you must do it on front-end, i would do this:

=aggr( only({<group = {'car'} >} brand), id)

=aggr( only({<group = {'bike'} >} brand), id)

I chose only() (assuming there is only 1 bike or car per id. You could choose concat() or maxstring() or whatever else you want to show in the case if there is > 1 record per id.

Re: How to Group By (Empty Values)

AshutoshBhumkar — Tue, 07 Sep 2021 15:49:50 GMT

Hi,

Maybe something like this.

Sample Dataset

Load * Inline [
ID,GROUP,BRAND
1,Car,RG
1,Bike,SETH
1,Airplane,SSRYN
2,Car,SHRTHB
2,Bike,SRHS
2,Airplane,SBF
3,Car,SNFBDF
3,Bike,NSFGBNSG
3,Airplane,SFHNSR
4,Car,NFGBNS
4,Bike,FGBSFGNB
4,Airplane,SFHNSFHN
5,Car,SFNSR
5,Bike,NSFHNSFH
5,Airplane,SNFHN ];

Output :

Expressions :

Car - Only({<GROUP = {"Car"}>}BRAND)

Bike - Only({<GROUP = {"Bike"}>}BRAND)

Airplane - Only({<GROUP = {"Airplane"}>}BRAND)

Thanks,

Ashutosh

Re: How to Group By (Empty Values)

virus190 — Tue, 07 Sep 2021 16:02:36 GMT

thank you very much for the quick help, i only want to do some of the values, so the second is good and i only have only 1 value per id, so this works for me perfectly!