Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

demaina1995
New Contributor II

Visualisation with data from two associated tables

Hello,

I have two different tables, one represents a portfolio database and the other corresponds to production rate. I would like to have a pivot table as a visualisation showing rate*price in order to calculate the budget.

Table Base_Article

REFProgramCodeSalePrice
AA350NT100

Table Rates

ProgramCode01/01/201801/02/201801/03/2018
A350NT876

I used a Crosstable transformation on this second table to import it.Those tables are associated with the key : Program-Code

I would like to have the following table in my visualisation :

Program01/01/201801/02/201801/03/2018
A350=8*100=7*100=6*100

Do you have some idea how could I do that ?

I tried the formula : sum(Cadence*SalePrice) but it doesn't work

I attached an example file.

Thanks

1 Solution

Accepted Solutions
ericasense
New Contributor III

Re: Visualisation with data from two associated tables

Hi Caroline,

Qlik's data engine is associative by nature and should link two separate tables in your data model automatically without having to perform joins in the script

It uses fieldnames that are exactly the same to perform the matching, so as long as the key is exactly the same (including case) in both tables then it will automatically join them and you can perform the expression

Script:

Base_Article:

LOAD Program & Code as kyProgramCode, *;

LOAD    * FROM [lib://DL/bdd_test.xlsx]  (ooxml, embedded labels, table is BASE_ARTICLE);


//"unpivot" the original table using crosstable

Rates:

CrossTable(tmpDate,Rate)

LOAD

    Program & Code as kyProgramCode,

    "43101",

    "43132",

    "43160",

    "43191",

    "43221",

    "43252",

    "43282",

    "43313",

    "43344",

    "43374",

    "43405",

    "43435"

FROM [lib://DL/bdd_test.xlsx]

(ooxml, embedded labels, table is Rates);


//reload the table with re-formatted dates

Left join (Rates) Load kyProgramCode, date(num#(tmpDate)) as Date, tmpDate resident Rates;

drop field tmpDate;



Expression in app:

=sum(SalesPrice*Rate)


Is this what you are trying to do?


Erica

5 Replies
justinphamvn
Contributor II

Re: Visualisation with data from two associated tables

Hi,

Try this:

  1. Load Base_Article
  2. Cross Table Rates (Program, Code, Date, Cadence)
  3. Join 2 table : Base_Article & Rates
  4. Add column sum(Cadence * SalePrice)
  5. Drop table
  6. Done

Hope this helps.

Justin.

ericasense
New Contributor III

Re: Visualisation with data from two associated tables

Hi Caroline,

Qlik's data engine is associative by nature and should link two separate tables in your data model automatically without having to perform joins in the script

It uses fieldnames that are exactly the same to perform the matching, so as long as the key is exactly the same (including case) in both tables then it will automatically join them and you can perform the expression

Script:

Base_Article:

LOAD Program & Code as kyProgramCode, *;

LOAD    * FROM [lib://DL/bdd_test.xlsx]  (ooxml, embedded labels, table is BASE_ARTICLE);


//"unpivot" the original table using crosstable

Rates:

CrossTable(tmpDate,Rate)

LOAD

    Program & Code as kyProgramCode,

    "43101",

    "43132",

    "43160",

    "43191",

    "43221",

    "43252",

    "43282",

    "43313",

    "43344",

    "43374",

    "43405",

    "43435"

FROM [lib://DL/bdd_test.xlsx]

(ooxml, embedded labels, table is Rates);


//reload the table with re-formatted dates

Left join (Rates) Load kyProgramCode, date(num#(tmpDate)) as Date, tmpDate resident Rates;

drop field tmpDate;



Expression in app:

=sum(SalesPrice*Rate)


Is this what you are trying to do?


Erica

Luminary
Luminary

Re: Visualisation with data from two associated tables

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

demaina1995
New Contributor II

Re: Visualisation with data from two associated tables

Thanks a lot !

Should I see a difference in the table after the "join" ?

It works, I just have a new issue with data that appears with "-" instead of the value. Do you know how to fix this issue ?

blank.png

ericasense
New Contributor III

Re: Visualisation with data from two associated tables

I think its to do with the data that you sent me.

In the Base_Article, for the Product A330-NEO there is only sale price when the Code = NZF

snip1.PNG

There is no corresponding row in the Rates table for this combination of product and code.

snip 2.PNG

Therefore they will not match and will return only null.

Erica

Community Browser