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: 
psk180590
Creator III
Creator III

Add Data Where NotExists or Where ISNull

Hello All,

I have the below data

Raw Data:

VisitIDProductFamilyAnswer
1ProductA10
1ProductB20
1ProductC10
2ProductA20

Expected Result:

Visit IDProduct FamilyAnswer
1ProductA10
1ProductB20
1ProductC10
2ProductA20
2ProductB0
2ProductC0

The Result table has two new rows where against ProductB and C when these Products don't have any entry against them in the Data table.

Which is the optimal way of acheiving this: Not Exists or IsNull?

Thanks

Sai.

1 Solution

Accepted Solutions
sunny_talwar

Check if this gives any better performance

thread_300761:

LOAD *,

AutoNumber(VisitID&ProductFamily) as Key

INLINE [

    VisitID, ProductFamily, Answer

    1, ProductA, 10

    1, ProductB, 20

    1, ProductC, 10

    2, ProductA, 20

];


Temp:

LOAD Distinct VisitID

Resident thread_300761;


Join (Temp)

LOAD Distinct ProductFamily

Resident thread_300761;


Concatenate (thread_300761)

LOAD *,

0 as Answer

Resident Temp

Where not Exists(Key, AutoNumber(VisitID&ProductFamily));


DROP Table Temp;

View solution in original post

7 Replies
keerthika
Creator II
Creator II

Hi,

     I hope the entry Product B & C for visit ID 2 is not in your qvd. For this you can use IsNull or try NVL function.

psk180590
Creator III
Creator III
Author

Hi,

My DataSource is not Oracle. So, probably NVL won't help me here.

keerthika
Creator II
Creator II

Hi,

    Okay . you can use IsNull function. can you share your app

psk180590
Creator III
Creator III
Author

Somehow IsNull doesn't work for me.

I have attached sample data and App. Please, have a look.

YoussefBelloum
Champion
Champion

Hi,

isnull and not exist will not work here because the line don't even exist

take a look at the attached

psk180590
Creator III
Creator III
Author

Hi,

Your solution almost works fine, but, it takes too long i have a total of 75,000+ Visit IDs and the script has been running from the past one hour.

Any ideas on how to better the performance aspect. Thanks!!

sunny_talwar

Check if this gives any better performance

thread_300761:

LOAD *,

AutoNumber(VisitID&ProductFamily) as Key

INLINE [

    VisitID, ProductFamily, Answer

    1, ProductA, 10

    1, ProductB, 20

    1, ProductC, 10

    2, ProductA, 20

];


Temp:

LOAD Distinct VisitID

Resident thread_300761;


Join (Temp)

LOAD Distinct ProductFamily

Resident thread_300761;


Concatenate (thread_300761)

LOAD *,

0 as Answer

Resident Temp

Where not Exists(Key, AutoNumber(VisitID&ProductFamily));


DROP Table Temp;