Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
satishqlik
Contributor 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 ??

Tags (1)
3 Replies

Re: records

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
Honored Contributor III

Re: records

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
Contributor II

Re: records

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