Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peschu123
Partner - Creator III
Partner - Creator III

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

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

6 Replies
swuehl
MVP
MVP

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
Employee
Employee

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
MVP
MVP

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
Creator
Creator

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
Partner - Creator III
Partner - Creator III
Author

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:

solutions_qv.jpg

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
Employee
Employee

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

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

swuehl is really best solution