Unique Record Selection

[#1]

Hi,

We will be entering CR no's in one Cell of excell sheet with Comma Separation. My requirement is to count total no of such entries and count unique instances of the CR in that given list

For example … I will have any entry as 162345,172387,185423, 162345.. In this case total count will be 4 and 3 are Unique. Also we may have sum up this across 3 Cells also

Can QlikView Support this request..

Thanks,
Soma

[#2 · Accepted solution]

Hi Soma,

In QlikView you could create an expression similar to

COUNT(DISTINCT [CR No])

and this will give you a distinct count of 3 as per your example.

I hope this is of some help.

Rod

[#3]

Hi Rod,

Thanks for QUick response. Does this take multiple values from single cell ?

Thanks,

Soma

[#4]

Hi Soma,

If you have multiple CR No's in each cell then QlikView will treat each the contents of each cell as an element within that field. If you need to identify each CR No seperately then I think you will need to split the contents of the cell apart.

In the script that loads the data into QlikView you may be able to use the SUBFIELD command to split the cells but to be honest I have not used this function for such a process before.

I hope this is of some help.

Rod

[#5]

Hi Rod,

Thanks . I will try out and update the forum

Thanks,
Soma

[#6]

Hi,

There is a function in scripting called 'SubField' which can be used to break a string which has delimited text.

Simply use this code to create a new field and then apply count on this field to get desired result:

T:
Load
SubField(CR_NO,',') as CR
From xyz;