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: 
francescopuppin
Contributor III
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;

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

2 Replies
swuehl
MVP
MVP

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

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.