Combination Count+Distinct+Modifier

[Anonymous]

Hi,

I wan to extract unique values out of the label "ID-LeadID". In the data base I loaded the actual status of all existing Leads (whatever the actual status is) and the changes. I only want the data defined selections:

({$<Kone_Country__c={Germany},Status={qualified},IsConverted={0},IsDeleted={0},Business_Type__c={"Doors","VA Repairs"}>}

For this selection there are 126 unique Leads with 317 changes.

As I want to know the unique number of Leads, I tried this Formula:

Count({$<Kone_Country__c={Germany},Status={qualified},IsConverted={0},IsDeleted={0},Business_Type__c={"Doors","VA Repairs"}>}[Id-LeadId])

The result is 317, as expected (changes). I tried several combinations of formula with distinct (many different positions in the formula), but I did not get once the result of 126 unique values.

The Result was either 0 or 317 😞

Can someone please help, thanks!

BR from Germany

Benjamin

[sunny_talwar]

Did you try this

Count(DISTINCT {$<Kone_Country__c={Germany},Status={qualified},IsConverted={0},IsDeleted={0},Business_Type__c={"Doors","VA Repairs"}>} [Id-LeadId])

[sunny_talwar]

Also, Distinct count of Id-LeadId is 127 or is a portion of Id-LeadId's distinct count is 127? Little confused

[Anonymous]

‌i did the formula, but the result was still 317 😞

this was also my first guess, but it wasn‘t the right solution.

Is may another position possible for the distinct value weithin the formula the Key?

May i give some more Informations about the tables loaded for understanding.

The data are loaded in two tables:

Table A (table for actual status):

Lead id (unique=126)/ status / business type c/ Country

Table B (table for changes)

lead id (not unique=317 changes for the 126 lead)/ isconverted/isdeleted/

[marcus_sommer]

Is the lead-id the key between the tables A and B or is there a different key and the lead-id fields in both tables are different? Are further tables connected to these tables? I think a screenshot from the tableviewer would be helpful.

- Marcus

[Anonymous]

Hi,

the lead id key is between table A (lead) and table B (Lead history) and the keys are not different.

There are other tables connected, but the formula contains no request to other tables.

The Key link between the tables is strong as you can see in the screenshot:

Hopefully this will help.

Thanks

Benjamin

[marcus_sommer]

I believe the general qlik recommendation is not to use key-fields within calculations else to use duplicated fields for it, for example:

...

Lead.Id as UniqueLeadID,

autonumber(Lead.Id) as KeyBetweenTables,

...

and by larger datasets will be an autonumber-key result in smaller RAM consumptions and therefore with better performance.

- Marcus