Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alihasan2018
New Contributor

Counting the distinct values

Counting of distinct concatenated values of Address line, State, Country, Pincode.

[Sheet2]:

LOAD

[Name],

[Bank Acc Number],

[Address line],

[State],

[Country],

[Pincode],

    Count(DISTINCT [Address line]&[State]&[Country]&[Pincode]) as count1,

    If(count1>1, [Name]),

    [Phone],

APPLYMAP( '__countryCodeIsoThree2Polygon', UPPER([Country]), '-') AS [Sheet2.Country_GeoInfo]

FROM [lib://Qlik Dashboards/Test_file.xlsx]

(ooxml, embedded labels, table is Sheet2);

1 Reply
agigliotti
Honored Contributor II

Re: Counting the distinct values

maybe this:

[Sheet2]:

LOAD *,

If(count1>1, [Name]);

LOAD

[Name],

[Bank Acc Number],

[Address line],

[State],

[Country],

[Pincode],

    Count(DISTINCT [Address line]&[State]&[Country]&[Pincode]) as count1,

    [Phone],

APPLYMAP( '__countryCodeIsoThree2Polygon', UPPER([Country]), '-') AS [Sheet2.Country_GeoInfo]

FROM [lib://Qlik Dashboards/Test_file.xlsx]

(ooxml, embedded labels, table is Sheet2);