Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Jsobrinho77
Creator
Creator

concat total distinct table

Hi guys,

I have a dimension that contains 3 values: Region1; Region2; Region3.

I have this example: ASIA/AUSTRALIA; JAPAN; JAPAN

The expected result is:  

 ASIA/AUSTRALIA; JAPAN

This is possible?

@Andrei_Cusnir  could you please, help me?

 

Labels (3)
5 Replies
Taoufiq_Zarra

@Jsobrinho77  many solution

for example in load script you can use this code:

Data:

load *,subfield(Value,';') as Newsubfield;
load *,rowno() as idtmp inline [
Value
ASIA/AUSTRALIA; JAPAN; JAPAN
];

output:

load concat(distinct Newsubfield,';') as Value resident Data group by idtmp;

drop table Data;

 

output:

Taoufiq_Zarra_0-1660594972140.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Jsobrinho77
Creator
Creator
Author

First of all, thank you for your answer..

Works fine.

I have one more question because the sort needs to be by 'dimension' always Region1, Region2, and Region3. Sometimes, its sorting by content and not by dimension for example ContentRegion2;X_ContentRegion1

So I would like always show the sort by 'dimension': X_ContentRegion1;ContentRegions2

This is possible?

marcus_sommer

You may try it with a slight tweak of the suggestion from @Taoufiq_Zarra, for example:

Data:

load *,subfield(Value,';', iterno()) as Newsubfield, iterno() as IterNo
while iterno() <= rangesum(substringcount(Value, ';'), 1);
load *,recno() as idtmp inline [
Value
ASIA/AUSTRALIA; JAPAN; JAPAN
];

output:

load concat(distinct Newsubfield,';', IterNo) as Value resident Data group by idtmp;

drop table Data;

- Marcus

 

Jsobrinho77
Creator
Creator
Author

First of all, thank you for your answer.

I think is almost there, the result is ASIA/AUSTRALIA; JAPAN; JAPAN because the distinct is by IterNO. It's possible to get the IterNo by distinct Newsubfield? So I think the right result.

I think the expected IterNo is only 1 and 2

Untitled.png

marcus_sommer

I did not consider that you need to concat the parts distinct - and that the normal IterNo would break it. This means the above tweak needs a further tweak, for example with:

Data:
load *, autonumber(idtmp & ' | ' & Newsubfield) as IterNoDistinct;
load *,subfield(Value,';', iterno()) as Newsubfield, iterno() as IterNo
while iterno() <= rangesum(substringcount(Value, ';'), 1);
load *,recno() as idtmp inline [
Value
ASIA/AUSTRALIA; JAPAN; JAPAN
];

output:
load concat(distinct Newsubfield,';', IterNoDistinct) as Value resident Data group by idtmp;

drop table Data;

- Marcus