Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

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

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;

6 Replies
MVP
MVP

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

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

];

Employee
Employee

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

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

MVP
MVP

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

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
Contributor

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

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
Contributor III

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

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 -

Employee
Employee

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

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

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

swuehl is really best solution

Community Browser