topic Re: Table join using a variable in QlikView

Table join using a variable

Fri, 21 Apr 2017 12:31:27 GMT

I am trying to join 2 tables together using a variable based on a selection in the app using the below script.

The variable is called VTemplateCode I want to join this to another table using a key of this variable code and SKU.

LOAD

$(VTemplateCode) & PK_SKU as Tkey

I am trying to be able to say if I switch a customer onto a different pricing platform (template code) what would the impact be. The first table contains current price and qty the 2nd table contains a list of templates with their prices.

Is this possible?

I could add the extra prices to table 1 but would like to see how to do it in qlik

Re: Table join using a variable

el_aprendiz111 — Fri, 21 Apr 2017 13:51:42 GMT

Send a sample of data please

Re: Table join using a variable

Fri, 21 Apr 2017 14:11:02 GMT

Below shows some sample data. In the App there is a variable where I can select a template. I then want to be able to pull a price from table 2 in order to calculate an impact. So if I select 12BM in the variable customer 1234 would bring back the 12BM price of 1.2 for product 123456 which would give an impact of £2.

Re: Table join using a variable

el_aprendiz111 — Sat, 22 Apr 2017 17:35:12 GMT

Table1:
LOAD *, AutoNumber(Template & [curret price]) AS KEY;
LOAD Customer,
SKU,
[curret price],
Qty,
Template
FROM
[..\..\Desktop\web_Qlik\Resultado10\Active.xlsx]
(ooxml, embedded labels, table is Table1);

NoConcatenate

Table2:
LOAD *, AutoNumber(Template & Price) AS KEY_t2;
LOAD Template,
SKU,
Price
FROM
[..\..\Desktop\web_Qlik\Resultado10\Active.xlsx]
(ooxml, embedded labels, table is Table2);

NoConcatenate

[KEYS]: LOAD Concat(KEY,',') AS KEY Resident Table1;

LET v_IKeys = trim(Peek('KEY'));

Mapping T2: LOAD Template,SKU & ';' & Price Resident Table2 Where not Match(KEY_t2, $(v_IKeys));

sumary:
LOAD *,
SubField(ApplyMap('T2',Template,Null()),';',1) AS New_SKU,
SubField(ApplyMap('T2',Template,Null()),';',-1) AS New_Price

Resident Table1;

DROP Table Table1,Table2,KEYS;

Re: Table join using a variable

Peter_Cammaert — Sun, 23 Apr 2017 15:47:36 GMT

If you want to do this "live" in a document and without a reload, you can use a link table with all possible connections from Customers & current product prices (table1) to Products & Pricing templates (table 2).

The only limitation will be that making selections for a different pricing scheme (for example to compare this one to the current one in a what-if analysis) will either apply to all selected customers (not good) or will force you to first select a single customer before making any comparisons.