Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Contributor III
Contributor III

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

View solution in original post

5 Replies
justinphamvn
Creator II
Creator II

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
Contributor III
Contributor III

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

pablolabbe
Luminary Alumni
Luminary Alumni

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

Anonymous
Not applicable
Author

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
Contributor III
Contributor III

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