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.

francescopuppin
New Contributor III

IF NOT EXISTS - Do the fields need to have the same name?

Hi Everybody,

maybe someone help with the "IF NOT EXISTS" syntax. Going through the previous posts, I have noticed that this EXISTS command makes the assumption that the field must have the same name (or, did I get It wrong?). But this implies that the model will make an association, or even a concatenation. But instead, I want to keep the fields independent.

I have created a nice example about a dance competition. This example reproduces EXACTLY the business problem that I have to solve at work. Here it is.

THE PROBLEM:

There is a dance competition. Only the first 3 solo dancers (male or female) and the first 3 couples will win the admission to the final.

The solo competition is the most important. If a dancer is participating and winning in both, he will be automatically admitted to the solo final, and his couple will be eliminated.

The jury is issuing a ranking of the first 6 solo dancers and the first 6 couples. The task is to present the list of the 3 solo winners and the 3 couple winners, so that 9 distinct people can be sent to the final.

Here is the code:

SoloRanking:
LOAD * INLINE [
SoloRankNo, DancerNo
1, 15
2, 27
3, 33
4, 40
5, 41
6, 42
];


CoupleRanking:
LOAD * INLINE [
CoupleRankNo, CoupleNo, MaleDancerNo, FeMaleDancerNo,
1, C03, 15, 50
2, C12, 51, 52
3, C15, 53, 54
4, C18, 55, 27
5, C31, 56, 57
6, C36, 58, 59
]; 

The desired output for the CoupleRanking is C12, C15, C31 (the couples C03 and C18 are eliminated, because one of the 2 dancers won the other competition)

My problem is how to discard the couples C03 and C18?!

Can anyone help?

Thanks,

Francesco

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


NOTE:


Without using the EXISTS command I have solved the problem as follows. But I am sure that there is a much more elegant solution..


MY TEMPORARY SOLUTION:

AllDancers:
LOAD
MaleDancerNo AS Dancer
RESIDENT
CoupleRanking;

Concatenate

AllDancers_concat:
LOAD
FeMaleDancerNo AS Dancer
RESIDENT
CoupleRanking;

LEFT JOIN (AllDancers)

DancersToExclude:
LOAD
DancerNo AS Dancer,
'Exclude' AS FlagToExclude
RESIDENT
SoloRanking;

ValidCoupleRanking:
LOAD
CoupleRankNo AS [Valid Couple Rank No],
CoupleNo AS [Valid Couple No],
MaleDancerNo AS [Valid Male Dancer],
FeMaleDancerNo AS [Valid Female Dancer]
RESIDENT
CoupleRanking;

INNER JOIN (ValidCoupleRanking)

ValidMaleDancers:
LOAD
Dancer  AS [Valid Male Dancer],
'Valid Male'  AS MaleDancerValidFlag
RESIDENT
AllDancers
WHERE LEN(FlagToExclude)=0
;

INNER JOIN (ValidCoupleRanking)

ValidFemaleDancers:
LOAD
Dancer  AS [Valid Female Dancer],
'Valid Female'  AS FemaleDancerValidFlag
RESIDENT
AllDancers
WHERE LEN(FlagToExclude)=0
;

DROP TABLE AllDancers;

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: IF NOT EXISTS - Do the fields need to have the same name?

Maybe like this?

SoloRanking:

LOAD * INLINE [

SoloRankNo, DancerNo

1, 15

2, 27

3, 33

4, 40

5, 41

6, 42

] where SoloRankNo <= 3;

CoupleRanking:

LOAD recno() as CoupleRankNoNew,* where recno() <=3;

LOAD *

INLINE [

CoupleRankNo, CoupleNo, MaleDancerNo, FeMaleDancerNo,

1, C03, 15, 50

2, C12, 51, 52

3, C15, 53, 54

4, C18, 55, 27

5, C31, 56, 57

6, C36, 58, 59

]

where

     not exists(DancerNo, MaleDancerNo)

     and

     not exists(DancerNo, FeMaleDancerNo)

2 Replies
MVP
MVP

Re: IF NOT EXISTS - Do the fields need to have the same name?

Maybe like this?

SoloRanking:

LOAD * INLINE [

SoloRankNo, DancerNo

1, 15

2, 27

3, 33

4, 40

5, 41

6, 42

] where SoloRankNo <= 3;

CoupleRanking:

LOAD recno() as CoupleRankNoNew,* where recno() <=3;

LOAD *

INLINE [

CoupleRankNo, CoupleNo, MaleDancerNo, FeMaleDancerNo,

1, C03, 15, 50

2, C12, 51, 52

3, C15, 53, 54

4, C18, 55, 27

5, C31, 56, 57

6, C36, 58, 59

]

where

     not exists(DancerNo, MaleDancerNo)

     and

     not exists(DancerNo, FeMaleDancerNo)

francescopuppin
New Contributor III

Re: IF NOT EXISTS - Do the fields need to have the same name?

Excellent, thanks a lot!

This is exactly what I was looking for.

Now that you have sent me this example, I finally managed to understand the syntax and the example on the ref manual.

Basically, the thing that was not obvious to me, is that the [Expression] can also be a name of a different field,

Thanks a lot

F.