topic Re: script function to compare two fields and show values existing in one but not in the other in QlikView

script function to compare two fields and show values existing in one but not in the other

Anonymous — Mon, 23 Jul 2018 14:31:23 GMT

Hi community!

Is there a script function that allows me to compare two fields and show the values existing in one field but not in the other?

This is the situation:

An Item is been sold to specific shops.

This item, lets call it 'Original' item, can be replaced for another item, a 'Replacement' item (with a new Item code).
In the database it´s two different item codes, and this replacement item is sold to specific shops as well.

In theory, as one Item code is replacing the other, this replacement should be sold to (at least) the same shops as the original one.

The fields are: Ori.ItemID, Ori.ShopID, Replace.ItemID and Replace.ShopID.

Joins are working correctly and when I select an original Item I can see the Shops where it was sold to, the new code replacing it and the shops where the replacement Item is been sold to.

What I need, is to compare the fields Original Shops and Replacement Shops so I can Identify when a Replacement Item is not being sold to shops where old item was.

Please see attached image.

I hope I made myself clear but if further information is needed, please let me know.

Thanks in advance.

Re: script function to compare two fields and show values existing in one but not in the other

ogautier62 — Mon, 23 Jul 2018 14:43:40 GMT

Hi,

there's nothing attached !

what is your model,

I think you have a table with : ItemID ans ShopID ?

regards

Re: script function to compare two fields and show values existing in one but not in the other

Anonymous — Mon, 23 Jul 2018 14:53:20 GMT

Hi Olivier.

You are correct about the model. Its a table with fields Ori.ItemID, Ori.ShopID, Replace.ItemID and Replace.ShopID, among others, but these are the ones I´m interested in.

I attached the file now.

Attachement is only a JPG file showing an example.

In this example, the original Item was sold to 6 shops, and replacement is being sold to 4, so there´s 2 shops where new Item is not being sold to and this is the kind of scenarios I need to identify and report.

Thanks for your time.

Best Regards.

Re: script function to compare two fields and show values existing in one but not in the other

vishsaggi — Mon, 23 Jul 2018 15:08:30 GMT

Try like below in your script:

Original:

LOAD *

FROM yourOriginalItemTableName;

Replacement:

LOAD *, IF(Exists(ShopCode), 'ItemsSold', 'ItemsNotSold') AS SoldFlag

FROM yourReplacementTableName;