Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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???