Add an Field to identify diferent Values

[#1]

Hello,

i hope anyone can help me.

I have to load a Table with no possible unique key. So i want to add a field during the load script to make the loaded data unique.

example:

Original Tabel on SQL-Server

id	calculationID
1	124
1	122
1	123
2	524
2	525

Now i want to ad a field to identify the difernt calulationID's. The added nummbers should be in order to the calculationID.

expectet Result in QlikView:

id	calculationID	newFieldId
1	124	1
1	122	3
1	123	2
2	524	2
2	525	1

Is there any idea how to manage this?

thx for help!

werner

[#2]

Hi Werner,

try this one.

org:
LOAD * INLINE [
id, calculationID
1, 124
1, 122
1, 123
2, 524
2, 525
];

new:
LOAD
*
, IF(ISNULL(PEEK(id)), 1, IF(id=PEEK(id), PEEK(newFieldId)+1, 1)) AS newFieldId
;
NOCONCATENATE
LOAD
id
, calculationID
RESIDENT org
ORDER BY id Asc, calculationID Desc;
DROP TABLE src;

Regards

Michael