correct the data

[somthingmendi123]

If I have an ITEMID that repeats several times, how do I correct the data so that each ITEMID will be unique

[Jack_Guo]

Hi @somthingmendi123 ,

Have you tried to use Distinct  ?

[somthingmendi123]

I have not tried
I would love to learn how to do this
I started using Click not long ago

Show me how I can do this

thשnks

[stevedark · Accepted solution]

Hi,

You can do this by loading the table ordered by the ITEMID and then using the PEEK function to look at the previous row to decide whether this row is a repeat of the previous line.

If you create a new app and run this code it will demonstrate the principle and the code:

tmpTable:
LOAD
	ITEMID
INLINE [
ITEMID
A
B
C
C
D
D
D
E
F];

Table:
LOAD
	*,
    tmpITEMID & if(Repeat > 1, ' (' & Repeat & ')', '') as ITEMID
    ;
LOAD
	ITEMID as tmpITEMID,
    if(Peek(tmpITEMID, -1) = ITEMID, peek(Repeat, -1) + 1, 1) as Repeat
RESIDENT tmpTable
ORDER BY ITEMID;

DROP TABLE tmpTable;

DROP FIELDS tmpITEMID, Repeat;

Obviously your table will have more than just the one column in it, but those will get picked up along with the ITEMID.

If you are not so worried about numbering the ITEMID repeats you could just have this to create a unique ID:

LOAD
    ITEMID & RowNo() as UniqueKey,
    ITEMID,

Hopefully that gives you some things to try that will point you in the right direction.

Steve

https://www.quickintelligence.co.uk/blog/