5 Replies Latest reply: Jan 17, 2018 4:57 AM by Erica Whalley RSS

    Visualisation with data from two associated tables

    Caroline Huet

      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

        • Re: Visualisation with data from two associated tables
          Justin Pham

          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.

          • Re: Visualisation with data from two associated tables
            Erica Whalley

            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

            • Re: Visualisation with data from two associated tables
              Pablo Labbe

              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