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: 
satishqlik
Creator II
Creator II

records

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 ??

3 Replies
sunny_talwar

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;

sasiparupudi1
Master III
Master III

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;

satishqlik
Creator II
Creator II
Author

Thank you Sunny for your response.

Also can you look @ the below issue.?

pls help

Suggest me where I did wrong completely I got stucked to figure out.

Can you help me out from this???