Add Data Where NotExists or Where ISNull

[psk180590]

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.

[keerthika]

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]

Hi,

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

[keerthika]

Hi,

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

[psk180590]

Somehow IsNull doesn't work for me.

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

[YoussefBelloum]

Hi,

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

take a look at the attached

[psk180590]

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 · Accepted solution]

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;