topic Re: Numbering unique values in QlikView

Numbering unique values

Saro_2306 — Tue, 09 Feb 2021 17:43:11 GMT

Hi all,

Data	Result
1	1
2	1
2	0
3	1
3	0
3	0
4	1
4	0
4	0

As shown in table I need to number 1 for 1st value and 0 for duplicate values. Can body help. Thanks in advance,

Re: Numbering unique values

edwin — Tue, 09 Feb 2021 18:49:34 GMT

two possible ways,

you can do an if(peek(Data) =Data then this is not the first instance
or if you have a large data set, i would add a ROWNO() field; left join the min(RowNo) group by Data - that will be the first instance

Re: Numbering unique values

Saro_2306 — Tue, 09 Feb 2021 18:51:52 GMT

Expression for below result also useful for me.

Re: Numbering unique values

PradeepK — Tue, 09 Feb 2021 19:15:22 GMT

You can do this in so many ways.. below is an example with Previous() function

Code Sample for Reference Only:

[Test]: Load * Inline [ data_org 1 2 2 3 3 3 4 4 4 ]; NoConcatenate [Result]: Load data_org, if(data_org = Previous(data_org),0,1) as Result; Load data_org Resident [Test] ORDER BY data_org ASC; Drop Table [Test];

Hope this helps!

Re: Numbering unique values

edwin — Tue, 09 Feb 2021 21:09:26 GMT

one way:

load if(peek(data)=data,peek(Seq)+1,1) as Seq, *; load * inline [ data 1,1 2,1 2,0 3,1 3,0 3,0 4,1 4,0 4,0 ];

Re: Numbering unique values

edwin — Tue, 09 Feb 2021 21:10:08 GMT

Re: Numbering unique values

edwin — Tue, 09 Feb 2021 21:14:58 GMT

however if you have a huge data set you wont want to loop and determine your sequence one row at a time:

data: load rowno()as rnum, data; load * inline [ data 1,1 2,1 2,0 3,1 3,0 3,0 4,1 4,0 4,0 ]; inner join (data) load data, min(rnum) as min Resident data group by data; newData: load data, rnum-min+1 as Seq Resident data; drop table data;

this gives you the same result but will be faster when you get to 10s of millions

Re: Numbering unique values

Saro_2306 — Wed, 10 Feb 2021 04:00:02 GMT

Thanks Pradeep, but above script only checks with previous value right, what if numbers are arranged in different order.

Re: Numbering unique values

Saro_2306 — Wed, 10 Feb 2021 04:16:34 GMT

Thanks Edwin, above script is not working when data shuffles itself.

like, 1,2,1,3,4,3,2,1.

Re: Numbering unique values

MayilVahanan — Wed, 10 Feb 2021 04:29:15 GMT

Hi @Saro_2306

Try like below

table:
LOAD * INLINE [
Data
1
2
1
3
4
3
2
1
];

Load Data, if(Data = Peek(Data), 0, 1) as Result Resident table order by Data;

DROP Table table;

Actually, order by data will sort the data column . so it will works.

Re: Numbering unique values

MayilVahanan — Wed, 10 Feb 2021 04:31:26 GMT

Hi @Saro_2306

For this,

Load Data, if(Data = Peek(Data), 0, 1) as Result, if(Data = Peek(Data), Peek('Result2')+1, 1) as Result2 Resident table order by Data;

Try like above, Result2 give solution

Re: Numbering unique values

edwin — Wed, 10 Feb 2021 13:33:00 GMT

it obviously has to be sorted. you need to supply more info. it could not be possible that you only have one column. you should order depending on your business rules. what fields are you sorting it by? is there a date field? is it based on the DATA field and load order?

here is a sample, if there are multiple fields but you do not want it re-ordered and still want the sequence based on DATA field:

load your data with a row no - this preserves the load order. then add a second row no order by data field and 1st row no. this way you can use the logic to sequence the data based on load order.

at this point you can use the peek function or the table operation (choice would depend on whether you have a huge data set. if you use the peek function and have your data in the high 10 millions you will realize peek is very slow

Re: Numbering unique values

PradeepK — Wed, 10 Feb 2021 14:57:48 GMT

maybe you missed this line in provided solution

ORDER BY data_org ASC;

In case you have sorting restriction and only want to work with data load order.. then it can be easily done with Mapping load and Recno().

Let me know if you have any other doubts.

Re: Numbering unique values

PradeepK — Wed, 10 Feb 2021 15:12:43 GMT

Use this if you have sorting constraints ...

[Test]: Load * Inline [ data_org 1 2 2 3 4 3 4 4 4 1 ]; Map_Test: Mapping Load data_org, Recno() as pos Resident [Test]; [Result]: Load data_org, if( Recno() = ApplyMap('Map_Test',data_org),1,0) as Result Resident [Test]; Drop Table [Test];

Hope this helps!