Field Contains Value from another Field Creating a Unique Group & Flag

[DataWhisperer]

Hi All,

So basically I have a [Control ID] field which is made up of several fields, one field being an [Account Number], so [Control ID], looks like this xxxx[Account Number]xxxx. 

I need to create a count for all [Control ID] which [Account Number] appears in more than once, and then group them into one unique value.

i.e. looks like this

Control ID	Account Number
14JT986701	986701
9JT7344	7344
67JT23453	23453
62JT986701	986701

and want it to return this 

Control ID	Account Number	Account Number Appears in No. of Control IDs	Unique Account Groups
14JT986701	986701	2	A
9JT7344	7344	1	B
67JT23453	23453	1	C
62JT986701	986701	2	A

[henrikalmen]

This does what you want for this specific example. If you have a lot of different Account Numbers you may get a problem with the last column.

data:
load * inline [
Control ID	Account Number
14JT986701	986701
9JT7344	7344
67JT23453	23453
62JT986701	986701
] (delimiter is '\t';

left join(data)
load 
   *, 
   window(sum(wildmatch([Control ID], '*'&[Account Number]&'*')), [Account Number]) as [Account Number Appears in No. of Control IDs],
   chr(64+autonumber([Account Number])) as [Unique Account Groups]
resident data;

The window function is relatively new to Qlik Sense. The wildmatch function will return the number 1 if the account number exists within the Control ID, otherwise it returns 0. I am summing that, and the windows function makes sure that the resulting value is imprinted on each row.

[Kushal_Chawda]

@DataWhisperer  I think as your control ID is made of Account No itself you don't really need to search anything in control id. You can simply take a count of account number and create group using numbers in case you have more than 26 accounts

Data:
load *,Window(count([Account Number]),[Account Number]) as [Account Number Appears in No. of Control IDs]
inline [
Control ID	Account Number
14JT986701	986701
9JT7344	7344
67JT23453	23453
62JT986701	986701 ] (delimiter is '\t');

New:
Load *,
     if([Account Number]<>Previous([Account Number]),
     rangesum(Peek('Unique Account Groups'),1),Peek('Unique Account Groups')) as [Unique Account Groups]
Resident Data
Order by [Account Number Appears in No. of Control IDs] desc,[Account Number];

Drop Table Data;

[henrikalmen]

Yes, that works if the Account Number always is part of the Control ID. That is the case in the given example, but I figure that a possibility is e.g. a pair Control ID=14KPQ and Account Number=2345 where there is no match. 

@DataWhisperer should provide some more information.

[henrikalmen]

Be aware that in my example, the way I create the field Unique Account Groups has a risk; the values may not be consistent in different app reloads if new data is added to the table, since I'm using the autonumber function to generate the values that you want.

[Qrishna]

Should work both in QV and QS. try below:

Data:
LOAD [Control ID],
[Account Number]
FROM
[https://community.qlik.com/t5/App-Development/Field-Contains-Value-from-another-Field-Creating-a-Uni...]
(html, codepage is 1252, embedded labels, table is @1);

MapNum:
Mapping
Load Distinct [Account Number],
'{' & 1 & '}' as Count_Flag_Text
resident Data;

Output:
Load *,
MapSubString('MapNum', [Control ID]) as MappedNum,
TextBetween(MapSubString('MapNum', [Control ID]), '{', '}') as Flag
resident Data;
drop table Data;

left join(Output)
Group:
Load *, chr(64+autonumber([Account Number Appears in No. of Control IDs] & [Account Number])) as [Unique Account Groups];
Load [Account Number],
sum(Flag) as [Account Number Appears in No. of Control IDs]
Resident Output
group by [Account Number];

drop fields MappedNum, Flag;

[Kushal_Chawda]

Yeah, true. As in original post it is mention that control id is made up of Account number, so I assumed control id always matches with account number. Let's what @DataWhisperer have to say