1 Reply Latest reply: Oct 11, 2017 9:25 AM by Andrea Gigliotti RSS

    Counting the distinct values

    Alihasan Sabuwala

      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);

        • Re: Counting the distinct values
          Andrea Gigliotti

          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);