Count the number of times a value occurs in a field

[bgled]

Hi,

I'd like to count the number of times a particular value from one set of data appears in a field of a second set of data. The second set of date is also linked to the data model.
I'm only have access to Qliksens as a contributor.

A simplified verion of the data would look like this:

Main data: this is the main set of data, with the first 3 columns coming straight from the source, the fourth column "Number of ECR" is what i want to calculate based on the second set of data.

PCR ID	Product Id	Dev Country	Number or ECR
PCR_01	AA1	Fr	2
PCR_02	BA2	Uk	3

Secondary data: the first 2 colmuns come from my second data source. I link this data to the main set using the first product from the "Impacted Products" column as the Key. That's how i retrieve the information found in the 3rd column

ECR ID	Impacted Products	Dev Country
ECR_05	AA1	Fr
ECR_10	BA2, FA3	Uk
ECR_21	AA1, BA2	Fr
ECR_22	BA2	Uk

My question is, how can i count, for each PCR ID, the number of times that the associated Product ID can be found in the Impacted Products column ?

I've tried this (a long with other variations), but it only counts the ones where the Impacted Products starts with the corresponding Product (2 and 2 in my example)

count({$<[PCR ID]=>} {<[ECR ID]=>} {$<[Impacted Products]={'=if(substringcount(Impacted Products, [Product Id])>0, "*"&[Product Id]&"*")'}>} [ECR ID])

Is it because of  way the secondary data has been linked to the data model ?

Thanks

[SBDataspark]

Hi there,

I'm not sure if the attached qvd serves your purpose, but it is worth the shot and helps me sharpen my skills...😁
I changed some things in the data model to calculate the number of ECRs.
Hopefully this can be applied to your situation.

Kind regards,

Sebb

[bgled]

Hi Sebb,

Thanks for taking the time! Unfortunately i myself can't do anthing with the QVF, only the deparment in charge of Qlik can help me.

But looking at your "ECR" table i think i get what you've done, and i actuallly talked about this with the "Qlik specialist' in charge of my application

• loading each ProductID from the ECR into a seperate row using the , as a delimeter

The only problem i have with this idea is that it then makes the data difficult to read, or atleast less friendly on the eyes.
Ie, having the same data repeated multiple times in multiple rows.
This could potentially be addressed with a Pivot Table, but that's not really the direction i wanted to go.

Maybe i could still use this technique for loading the ECR data, but then rebuild my original ECR table with a calculated dimension that merges the Product ID to create Impacted Products?

Thanks, i'll post back

[chris_djih]

---

Maybe i could still use this technique for loading the ECR data, but then rebuild my original ECR table

---

Yes, you could simply make a Mapping table with  PCRID and count(distinct ECR).
Furthermore have some reads on Subfield and Concat. These functions may solve similar situations if you run into them later.

[SBDataspark]

Hi Bgled,

Thank you for taking the time to look at my suggested solution!
I understand your wish for keeping the [Impacted Products] in tact as as combined field.

This can easily be added to the following piece of my load script:

[tmp1DataSet2]:
NoConcatenate Load
ECRID,
Trim(SubField([Impacted Products],',')) as [ProductID], // Trim the spaces from the [Impacted Products]
[Impacted Products] // Keep de [Impacted Products] as a combined column
Resident [tmpDataSet2];

This results in the following visual table in your work sheet:

Kind regards,

Sebb