topic Re: records in QlikView

records

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: records

sunny_talwar — Tue, 06 Mar 2018 12:31:00 GMT

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;

Re: records

sasiparupudi1 — Tue, 06 Mar 2018 13:49:45 GMT

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;

Re: records

satishqlik — Tue, 06 Mar 2018 16:05:01 GMT

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