concat total distinct table

[Jsobrinho77]

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?

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

[Jsobrinho77]

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]

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

[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