Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Table join using a variable

Hi

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

4 Replies
el_aprendiz111
Specialist
Specialist

Hi

Send a sample of data please

Not applicable
Author

Hi

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.

Qlik query.JPG

el_aprendiz111
Specialist
Specialist

Hi

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(TemplatePrice) 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;


kkeeyy.png

Peter_Cammaert

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.