Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table
temp:
LOAD*
Inline
[country, code
CN, 1
CN, 2
CN, 3
KR, 1
KR, 2
KR, 3
KR, 4
KR, 5
SA, 1
SA, 2
SA, 4
SA, 6
SA, 8
];
I want to see in the data model
only common codes in all the countries.
my output should be like this
country code
CN 1
CN 2
KR 1
KR 2
SA 1
SA 2
how do we accomplish this ??
Try this
temp:
LOAD * INLINE [
country, code
CN, 1
CN, 2
CN, 3
KR, 1
KR, 2
KR, 3
KR, 4
KR, 5
SA, 1
SA, 2
SA, 4
SA, 6
SA, 8
];
Left Join (temp)
LOAD code,
count(DISTINCT country) as Count
Resident temp
Group By code;
Right Join (temp)
LOAD Max(Count) as Count
Resident temp;
Try
temp:
LOAD * INLINE [
country, code
CN, 1
CN, 2
CN, 3
KR, 1
KR, 2
KR, 3
KR, 4
KR, 5
SA, 1
SA, 2
SA, 4
SA, 6
SA, 8
];
T1:
NoConcatenate LOAD code,
Concat(country,',') as TotalCountries,
Len(Concat(country,',')) as TotalCountriesLength
Resident temp
Group By code;
Inner Join(T1)
Load
FirstSortedValue(Distinct TotalCountries,-TotalCountriesLength) As TotalCountries
Resident T1
;
Final:
NoConcatenate Load
SubField(TotalCountries,',') As Country,
code
Resident T1;
Drop Table T1;
Thank you Sunny for your response.
Also can you look @ the below issue.?
Suggest me where I did wrong completely I got stucked to figure out.
Can you help me out from this???