topic Re: How would you replace a value with a new? in QlikView

How would you replace a value with a new?

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

Hi experts!

I have a problem that I would like to share and maybe hear your suggested sollutions!

I have 2 excel files with prices of products. One is a general list and the other is for clients that has a diffrent price from that general list.

I want to keep the general price for every client that does not appear in the second list.

Scriptwise, how do I make sure that the clients that appear in the secont list gets the right price?

Explaining further,

My existing table with general prices can not be altered.

What I want to do is to "overwrite" the price only for the product IDs that exist in the "new" list of prices.

So the new list consists of Product ID and the price whilst the general consists of many more fields per product ID row.I only want to alter the price field:

General prices table: (has every ProductID and their general price)

ProductID

Price

Field3

Field4

Field5

Etc..

Other prices excel:(has only a handfull rows with Products that have a new temporary price, only a few IDs from the general list exists here)

ProductID

Price

Re: How would you replace a value with a new?

Anil_Babu_Samineni — Fri, 20 Dec 2019 08:56:26 GMT

Check If that helps Exists in Qlik

Re: How would you replace a value with a new?

Kushal_Chawda — Fri, 20 Dec 2019 09:55:13 GMT

sample data with expected output will help better to get faster reply?

Re: How would you replace a value with a new?

H_Julian — Fri, 20 Dec 2019 10:26:13 GMT

Just join both lists with different Value names

for example:

[Table 1]:

Customer_ID,[General Value]

123,100

[Table 2]:

Customer_ID,[Customer Value]

123,70

Once joined you form a new Value Dimension which says:

if(isnull([Customer Value]),[General Value],[Customer Value]) as [New Value]

Then you got a list of all customers with either customer based values and if not given the general Values

Greetings

Re: How would you replace a value with a new?

Meg00 — Fri, 20 Dec 2019 14:26:33 GMT

Ah, sorry, I should have explained further.

The products come from a different source.

Products have names like

ABC-50

ABC-251

BG-100-100

etc.

The general price list is matched with the uppercase letters, so all ABC products have the same price and all the BG have the same price.

The general pricelist is the excel file with the uppercase letters as "productpricelist" and then price column.

The "special" price list is presented with the full unique product name ex ABC-222 but does not have the same price as the ABC general prices.

There is no customer ID in either list. Just the uppercase "Productpricelist" in the general one and the full product name in the special one.

Re: How would you replace a value with a new?

siddheshmane — Sun, 22 Dec 2019 11:18:07 GMT

Hi,

You can concatenate the two price lists to make one single table. Load the table using Mapping Load.

and then ApplyMap to you existing table. I've shared my code below. Hope this helps.

MAP_TABLE:

MAPPING LOAD * INLINE [
PRODUCT, PRICE
AA, 1000
ABC, 2000
AA-123, 4000
];

---------------AA and ABC is my general price

-----------------AA-123 is my special price

TEST_TABLE:

LOAD * INLINE [
PRODUCT, QUANTITY
AA-123, 100
AA-34-ABC, 200
ABC-111 - 30
];

FINAL_TABLE:

LOAD
PRODUCT,
APPLYMAP('MAP_TABLE',PRODUCT, APPLYMAP('MAP_TABLE', SubField(PRODUCT, '-',1))) AS VALUE,
QUANTITY
Resident TEST_TABLE;

DROP Table TEST_TABLE;

-------- My ApplyMap will get values for special price first. If I don't have a corresponding special price the ELSE part in the ApplyMap will be executed (which is my general price)

Re: How would you replace a value with a new?

Meg00 — Mon, 30 Dec 2019 09:08:24 GMT

Tried to explan further in post. I simply want to change the price field only for the Product IDs that exist in the new price excel file.

Re: How would you replace a value with a new?

siddheshmane — Mon, 30 Dec 2019 11:01:47 GMT

I would just add another applymap() in the else part of the main applymap() where you get your general prices.

I'll have two mapping tables one with general prices and the other with the special prices.

If i get the values in special price then ill applymap() the special prices table, else i'll use the general prices table.

Could you please share a sample data.

Re: How would you replace a value with a new?

Meg00 — Mon, 30 Dec 2019 11:13:49 GMT

Sorry, I can not provide sample data. the steps leading up to the general prices table are many and I do not want to change that part of the script. Only wish to replace prices if the ID exists in the new prices excel.