To make 2 output as 1 output

[Syahfira_Zakaria]

Hello. I need help how to solve this ruling.

I have Client and Partner Column.

So users wanna see together in one column who is the client and partner. So I concatenate both column and it gives me this output.

Client - xxx

Partner - yyy

Client_Partner = xxx_yyy

The thing is both xxx and yyy can appear in the opposite column which is,

Client - yyy

Partner - xxx

Client_Partner = yyy_xxx

The issue is both

xxx_yyy and yyy_xxx is one client_partner but since they can appear both in client and partner column, it generates the output.

Below image is for reference.

So, my question is, how i can make both Region_Combined2 as 1 output only instead of 2 in script or chart editor?

Thank you.

[PrashantSangle]

Hi,

try below code

Test:
LOAD
MyField, Concat(Key,'',Key) as Key
GROUP BY MyField;
Load MyField, Mid(MyField,IterNo(),1) as Key inline [
MyField
xxx_yyy
yyy_xxx
ppp_mmm
mmm_ppp
zzz_aaa
]
WHILE
IterNo() <= Len(MyField)
;

Regards,

Prashant Sangle

[Syahfira_Zakaria]

Hi Prashant,

Sorry, I couldn't understand your code. Could you help to explain further please ?

Is like this? But, it throws error.

[Syahfira_Zakaria]

Hi Prashant,

I have managed to run the code but not sure if it is right or no.

new:
load
EntityRegion,
PartnerRegion,
Concat(EntityRegion,'',PartnerRegion) as Key
Resident ResidentData
GROUP BY
EntityRegion,
PartnerRegion;

new2:
Load
EntityRegion,
PartnerRegion,
Mid(EntityRegion,IterNo(),1) as Key
Resident ResidentData;

new3:
load * inline [
EntityRegion, PartnerRegion, Key
]
WHILE
IterNo() <= Len(EntityRegion)
;

Drop Table ResidentData;

And this is the output that I got.

I dont know what I should do with this output.

[Rohan]

Hi,

Instead of using inline, fetch from your QVD/Data Source & this code is using preceeding load & not different loads of resident. The flow of the code is from bottom up. :

New:

//3rd

load

EntityRegion,
PartnerRegion,
Concat(EntityRegion,'',PartnerRegion) as Key
GROUP BY
EntityRegion,
PartnerRegion;

//2nd

Load
EntityRegion,
PartnerRegion,
Mid(EntityRegion,IterNo(),1) as Key;

//1st

load 
EntityRegion, PartnerRegion
From [YOURQVD]

WHILE
IterNo() <= Len(EntityRegion);

Regards,

Rohan.

[Syahfira_Zakaria]

Hi Rohan,

Thank you. But I think it is not solving the issue that Im facing as I see the values are doubled now.

[Rohan]

Hi @Syahfira_Zakaria ,

You have to pass that Concated field of EntityRegion & PartnerRegion, something like :

New:

//3rd

load

Combo,
Concat(Key,'',Key) as Key
GROUP BY
Combo;

//2nd

Load
Combo,
Mid(Combo,IterNo(),1) as Key;

//1st

load 
EntityRegion&'_'&PartnerRegion as Combo 
From [YOURQVD]

WHILE
IterNo() <= Len(EntityRegion&'_'&PartnerRegion);

Also you will have to Link this New table with your existing data model using :

EntityRegion&'_'&PartnerRegion as Combo; whereever you need to link it to.

Regards,

Rohan.

[Syahfira_Zakaria]

Hi Rohan, thank you. I have ran the code you suggested. I am still seeing the 2 output instead of 1.

I dont understand what's the difference its going to be when I used this Combo column to linked back to my fact table when the output in the Combo column and my concatenated column in fact table is same.

[Rohan]

Hi @Syahfira_Zakaria ,

You have to refer the "Key" column where you will have a single value against both of them.

Regards,

Rohan.