Mark/flag identical field values or just keep the first occurring value

[peschu123]

Hi,

I have the following table(INPUT) and I want it to look like OUTPUT1 or OUTPUT2 at the end.

INPUT: LOAD * INLINE [     Key1, Key2, value1     100, 1, a     100, 2, b     100, 3, a     100, 4, c     100, 5, a     100, 6, d     101, 1, a     101, 2, b     101, 3, c     101, 4, d     101, 5, c     101, 6, d     101, 7, c     101, 8, a ];

OUTPUT1: LOAD * INLINE [     Key1, Key2, value1, flag     100, 1, a, 1     100, 2, b, 1     100, 3, a, 2     100, 4, c, 1     100, 5, a, 3     100, 6, d, 1     101, 1, a, 1     101, 2, b, 1     101, 3, c, 1     101, 4, d, 1     101, 5, c, 2     101, 6, d, 2     101, 7, c, 3     101, 8, a, 2 ];

OUTPUT2: LOAD * INLINE [     Key1, Key2, value1     100, 1, a     100, 2, b     100, 3,     100, 4, c     100, 5,     100, 6, d     101, 1, a     101, 2, b     101, 3, c     101, 4, d     101, 5,     101, 6,     101, 7,     101, 8, ];

I would prefer Output1 but at the moment it is not important. I just have to make sure that I only take the first occurring value if there are 2 identical values with the same key1.

Background:

I need to do this, because I create generic tables afterwards which I join into another table. When there are 2 identical values I finally get much more rows to join than I want and need.   With a flag or null() I could simply use a Where ...

I appreciate any help on this...

regards, Peter

Message was edited by: Forgot the attachment, with the tables, if someone needs it.

[swuehl]

If you don't have to many combinations of Key1 and value1, you can use autonumber for option 1

INPUT:

LOAD *,AutoNumber(recno(), Key1&value1 ) as Flag  INLINE [

    Key1, Key2, value1

    100, 1, a

    100, 2, b

    100, 3, a

    100, 4, c

    100, 5, a

    100, 6, d

    101, 1, a

    101, 2, b

    101, 3, c

    101, 4, d

    101, 5, c

    101, 6, d

    101, 7, c

    101, 8, a

];

[Clever_Anjos]

If you want only flag the first ocorrence you can try this

INPUT:

LOAD *, Exists(Key2) as Flag INLINE [

    Key1, Key2, value1

    100, 1, a

    100, 2, b

    100, 3, a

    100, 4, c

    100, 5, a

    100, 6, d

    101, 1, a

    101, 2, b

    101, 3, c

    101, 4, d

    101, 5, c

    101, 6, d

    101, 7, c

    101, 8, a

];

Flag = 0 // First occorence

Flag = 1 // 2nd, 3nd...

[swuehl · Accepted solution]

And for your option 2 maybe:

INPUT:

LOAD *, Key1&value1 as TempKey INLINE [

    Key1, Key2, value1

    100, 1, a

    100, 2, b

    100, 3, a

    100, 4, c

    100, 5, a

    100, 6, d

    101, 1, a

    101, 2, b

    101, 3, c

    101, 4, d

    101, 5, c

    101, 6, d

    101, 7, c

    101, 8, a

];

RESULT:

Left Join (INPUT) LOAD

     Key1,

     Key2,

     TempKey as TempKey2,

     value1 as flagvalue

Resident INPUT

where not exists(TempKey2,TempKey);

drop fields TempKey, TempKey2;

[manas_bn]

Adding to the many good suggestions.

This is for Output 1

LOAD *
,RecNo() as ID                                     // Create ID Field
INLINE [
    Key1, Key2, value1
    100, 1, a
    100, 2, b
    100, 3, a
    100, 4, c
    100, 5, a
    100, 6, d
    101, 1, a
    101, 2, b
    101, 3, c
    101, 4, d
    101, 5, c
    101, 6, d
    101, 7, c
    101, 8, a
];

// Join Rank to Fact table
LEFT JOIN (Fact)
LOAD
ID
,if(value1=Previous(value1) and Key1=Previous(Key1),peek(Rank)+1,1) as Rank
RESIDENT Fact
ORDER BY value1,Key1,Key2  ;

Cheers!

[peschu123]

Hi again,

sorry that I needed so long to answer.

At first, thank you all so much for your contributions and the great work. I was on the totally wrong direction with my tries.

Since I was not sure whom to give the points I decided to try all the solutions. So I created a QVW and here you can see the results:

I attached the corresponding QVW file, so you can check it out by yourself. The solution No. 2 from Swuehl is exactly what I need. So I can integrate the whole thing in an earlier step in my script.

So swuehl got the "Correct Answer" and the other 2 a "Helpful Answer". In the script from Clever Anjos is something going wrong, looks like one of my earlier tries.

Thank you all very much and have a nice day.

- Peter -

[Clever_Anjos]

Checking here, my script is dependent of order of script.

If it´s the first load, it calculates ok.

swuehl is really best solution