topic Add Data Where NotExists or Where ISNull in QlikView

Add Data Where NotExists or Where ISNull

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have the below data

Raw Data:

VisitID	ProductFamily	Answer
1	ProductA	10
1	ProductB	20
1	ProductC	10
2	ProductA	20

Expected Result:

Visit ID	Product Family	Answer
1	ProductA	10
1	ProductB	20
1	ProductC	10
2	ProductA	20
2	ProductB	0
2	ProductC	0

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.

Re: Add Data Where NotExists or Where ISNull

keerthika — Wed, 09 May 2018 08:09:42 GMT

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.

Re: Add Data Where NotExists or Where ISNull

psk180590 — Wed, 09 May 2018 08:14:38 GMT

Hi,

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

Re: Add Data Where NotExists or Where ISNull

keerthika — Wed, 09 May 2018 08:30:38 GMT

Hi,

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

Re: Add Data Where NotExists or Where ISNull

psk180590 — Wed, 09 May 2018 08:39:41 GMT

Somehow IsNull doesn't work for me.

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

Re: Add Data Where NotExists or Where ISNull

YoussefBelloum — Wed, 09 May 2018 09:54:08 GMT

Hi,

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

take a look at the attached

Re: Add Data Where NotExists or Where ISNull

psk180590 — Wed, 09 May 2018 11:53:46 GMT

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

Re: Add Data Where NotExists or Where ISNull

sunny_talwar — Wed, 09 May 2018 12:04:43 GMT

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;