9 Replies Latest reply: Mar 2, 2018 4:46 AM by Florian Klostermeier RSS

    Calculate with Grouped (aggregated) variables during load

    Florian Klostermeier

      Hi,

       

      I am quite new using Qlik Sense. What I have done so far is to create a pivot table (see screenshot) showing some geographical areas in the rows and the corresponding score for each number of citizens (for 5 years) in the columns.

      Screenshot_Leitbereich.JPG

       

      The formula for the first column (ERWERB_2012) is:

      (Sum(ERWERB_2012)/Max(TOTAL Aggr(Sum(ERWERB_2012), LEITBEREICH)))*10


      How to read:

      ERWERB_2012 = the number of citizens in 2012

      LEITBEREICH = the geographical area


      What the formula does: I divide the number of citizens in each area by the max number of citizens of all areas.


      I would need these pivot table results right on the table level, as I can't use these pivot table results outside the pivot table.


      Is there a possibility to do this while loading the data? The loaded data table should look like the pivot table in the screenshot.


      Thanks,

      Florian


        • Re: Calculate with Grouped (aggregated) variables during load
          Ruben Marin

          Hi Florian usually will be better having a field called ERWERB related with a calendar table that gives the year of each record. And, if you're using a Sum it's beacuse there are many records for each LEITBEREICH and year, isn't?

           

          Beside that, the not mess this much I think you can calculate this in script using mappings to retrieve the max ERWERB_YEAR by LEITBEREICH, ie:


          // Retrieve max value for each year

          For vYear=2012 to 2016

          tmpMax:

          LOAD $(vYear) as Year

            Max(ERWERB_$(vYear)) as maxERWERB

          LOAD LEITBEREICH

            Sum(ERWERB_$(vYear)) as ERWERB_$(vYear)

          Resident DataTable

          Group by LEITBEREICH;

          NEXT


          // create mapping

          mapMax:

          Mapping LOAD Year,

          maxERWERB

          resident tmpMax;


          DROP Table tmpMax;


          // Create the table with results

          Result:

          LOAD LEITBEREICH

          Sum(ERWERB_2012)/Applymap('mapMax', 2012) *10 as result2012

          Sum(ERWERB_2013)/Applymap('mapMax', 2013) *10 as result2013

          ...

          Resident DataTable

          Group by LEITBEREICH;

           

          *Not tested*


            • Re: Calculate with Grouped (aggregated) variables during load
              Florian Klostermeier

              Hey Ruben,

               

              thank you very much for your quick and very helpful answer!
              I managed to include this in my syntax and it worked!

               

              The result is exactly what I need! Now I only have to adapt this to the other variables.

               

              One last question:
              Is it possible to use variables, like you did it with $year, to define a filter in the front end and start the LOAD script according to the filter chosen in the front end?

               

              Thanks and best,

              Florian

                • Re: Calculate with Grouped (aggregated) variables during load
                  Ruben Marin

                  I know it can be done in Qlikview, not sure if I made this in Sense but most probably it should work.

                   

                  $(VariableName) is converted to the content of VariableName

                    • Re: Calculate with Grouped (aggregated) variables during load
                      Florian Klostermeier

                      Hi Ruben,

                       

                      ok, I will check this.

                      Concerning the syntax you wrote. Beside the variable ERWERB, there a many other variables I would like to calculate according to your syntax.

                       

                      Please find below your syntax as well as the list of the variables I would need to import as well:

                       

                      -------------------------------------

                       

                      // Load raw data

                      ROHDATEN:

                      LOAD

                          PLZ,

                          LEITBEREICH,

                          ERWERB_2012,

                          ERWERB_2013,

                          ERWERB_2014,

                          ERWERB_2015,

                          ERWERB_2016,

                          BETRIEBE_2012,

                          BETRIEBE_2013,

                          BETRIEBE_2014,

                          BETRIEBE_2015,

                          BETRIEBE_2016,

                          EINWOHNER_2012,

                          EINWOHNER_2013,

                          EINWOHNER_2014,

                          EINWOHNER_2015,

                          EINWOHNER_2016,

                          EINWOHNER_AB60_2012,

                          EINWOHNER_AB60_2013,

                          EINWOHNER_AB60_2014,

                          EINWOHNER_AB60_2015,

                          EINWOHNER_AB60_2016,

                          GENEHMIGUNGEN_TOTAL_2012,

                          GENEHMIGUNGEN_TOTAL_2013,

                          GENEHMIGUNGEN_TOTAL_2014,

                          GENEHMIGUNGEN_TOTAL_2015,

                          GENEHMIGUNGEN_TOTAL_2016,

                          GENEHMIGUNGEN_WOHNBAU_2012,

                          GENEHMIGUNGEN_WOHNBAU_2013,

                          GENEHMIGUNGEN_WOHNBAU_2014,

                          GENEHMIGUNGEN_WOHNBAU_2015,

                          GENEHMIGUNGEN_WOHNBAU_2016,

                          GENEHMIGUNGEN_GEWERBE_2012,

                          GENEHMIGUNGEN_GEWERBE_2013,

                          GENEHMIGUNGEN_GEWERBE_2014,

                          GENEHMIGUNGEN_GEWERBE_2015,

                          GENEHMIGUNGEN_GEWERBE_2016,

                          GENEHMIGUNGEN_EFH_2012,

                          GENEHMIGUNGEN_EFH_2013,

                          GENEHMIGUNGEN_EFH_2014,

                          GENEHMIGUNGEN_EFH_2015,

                          GENEHMIGUNGEN_EFH_2016,

                          GENEHMIGUNGEN_MFH_2012,

                          GENEHMIGUNGEN_MFH_2013,

                          GENEHMIGUNGEN_MFH_2014,

                          GENEHMIGUNGEN_MFH_2015,

                          GENEHMIGUNGEN_MFH_2016,

                          MFH_ETAGE_1_2012,

                          MFH_ETAGE_1_2013,

                          MFH_ETAGE_1_2014,

                          MFH_ETAGE_1_2015,

                          MFH_ETAGE_1_2016,

                          MFH_ETAGE_2_2012,

                          MFH_ETAGE_2_2013,

                          MFH_ETAGE_2_2014,

                          MFH_ETAGE_2_2015,

                          MFH_ETAGE_2_2016,

                          MFH_ETAGE_3_2012,

                          MFH_ETAGE_3_2013,

                          MFH_ETAGE_3_2014,

                          MFH_ETAGE_3_2015,

                          MFH_ETAGE_3_2016,

                          MFH_ETAGE_4_2012,

                          MFH_ETAGE_4_2013,

                          MFH_ETAGE_4_2014,

                          MFH_ETAGE_4_2015,

                          MFH_ETAGE_4_2016,

                          MFH_ETAGE_AB5_2012,

                          MFH_ETAGE_AB5_2013,

                          MFH_ETAGE_AB5_2014,

                          MFH_ETAGE_AB5_2015,

                          MFH_ETAGE_AB5_2016,

                          MFH_ETAGE_AB3_2012,

                          MFH_ETAGE_AB3_2013,

                          MFH_ETAGE_AB3_2014,

                          MFH_ETAGE_AB3_2015,

                          MFH_ETAGE_AB3_2016,

                          BESCHAEFTIGUNG_2012,

                          BESCHAEFTIGUNG_2013,

                          BESCHAEFTIGUNG_2014,

                          BESCHAEFTIGUNG_2015,

                          BESCHAEFTIGUNG_2016,

                          KAUFKRAFT_2012,

                          KAUFKRAFT_2013,

                          KAUFKRAFT_2014,

                          KAUFKRAFT_2015,

                          KAUFKRAFT_2016

                      FROM [lib://Ordner Externe Daten/Import_Qlik.xlsx]

                      (ooxml, embedded labels, table is Tabelle1);

                       

                      // Retrieve max value for each year

                      For vYear=2012 to 2016

                      tmpMax:

                      LOAD $(vYear) as Year,

                        Max(ERWERB_$(vYear)) as maxERWERB;

                      LOAD LEITBEREICH,

                        Sum(ERWERB_$(vYear)) as ERWERB_$(vYear)

                      Resident ROHDATEN

                      Group by LEITBEREICH;

                      NEXT

                       

                      // create mapping

                      mapMax:

                      Mapping LOAD Year,

                      maxERWERB

                      resident tmpMax;

                      DROP Table tmpMax;

                       

                      // Create the table with results

                      Result:

                      LOAD LEITBEREICH,

                      Sum(ERWERB_2012)/Applymap('mapMax', 2012) *10 as ERWERB_2012,

                      Sum(ERWERB_2013)/Applymap('mapMax', 2013) *10 as ERWERB_2013,

                      Sum(ERWERB_2014)/Applymap('mapMax', 2014) *10 as ERWERB_2014,

                      Sum(ERWERB_2015)/Applymap('mapMax', 2015) *10 as ERWERB_2015,

                      Sum(ERWERB_2016)/Applymap('mapMax', 2016) *10 as ERWERB_2016

                      Resident ROHDATEN

                      Group by LEITBEREICH;

                       

                      -----------------------

                       

                      Can you tell me how to adapt this script to import / calculate all variables that way?

                       

                      Thanks,

                      Florian

                        • Re: Calculate with Grouped (aggregated) variables during load
                          Florian Klostermeier

                          Hi,

                           

                          I would like to change the syntax in my previous post from:

                           

                          For each vYear in 2012, 2013

                          tmpMax:

                          LOAD $(vYear) as KENNZAHL,

                            Max(ERWERB_$(vYear)) as MAXIMALWERT;

                          LOAD LEITBEREICH,

                            Sum(ERWERB_$(vYear)) as ERWERB_$(vYear)

                          Resident HEINZE_ROHDATEN

                          Group by LEITBEREICH;

                          NEXT

                           

                          to:

                           

                          For each vYear in ERWERB_2012, ERWERB_2013

                          tmpMax:

                          LOAD $(vYear) as KENNZAHL,

                            Max($(vYear)) as MAXIMALWERT;

                          LOAD LEITBEREICH,

                            Sum($(vYear)) as $(vYear)

                          Resident HEINZE_ROHDATEN

                          Group by LEITBEREICH;

                          NEXT

                           

                          But unfortunately, is crushes my syntax when I change it that way. Could you tell my why?

                           

                          Thanks,

                          Florian

                            • Re: Calculate with Grouped (aggregated) variables during load
                              Ruben Marin

                              Sorry, no time right now to look for a solution but I was thinking in somethin like you are doing.

                               

                              If you sustitute the valriable with it's contects you can see where there can be an error:

                              For each vYear in ERWERB_2012, ERWERB_2013

                              tmpMax:

                              LOAD ERWERB_2012 as KENNZAHL, // I suppose this should store only the year number

                                Max(ERWERB_2012) as MAXIMALWERT;

                               

                              Maybe with:

                              For each vYear in ERWERB_2012, ERWERB_2013

                              tmpMax:

                              LOAD Subfield('$(vYear)', '_', -1) as KENNZAHL,

                                Max($(vYear)) as MAXIMALWERT;

                                • Re: Calculate with Grouped (aggregated) variables during load
                                  Florian Klostermeier

                                  Hi,

                                   

                                  no worries, you have been more than helpful! Thank you very much!

                                   

                                  I managed to finalize the syntax so that instead of the year, all variables will be saved in the mapping file:

                                   

                                  // Retrieve max value for each year

                                  For each vYear in 'ERWERB_2012', 'ERWERB_2013'

                                  tmpMax:

                                  LOAD '$(vYear)' as KENNZAHL,

                                    Max($(vYear)) as MAXIMALWERT;

                                  LOAD LEITBEREICH,

                                    Sum($(vYear)) as $(vYear)

                                  Resident HEINZE_ROHDATEN

                                  Group by LEITBEREICH;

                                  NEXT

                                   

                                  // create mapping

                                  mapMax:

                                  Mapping LOAD KENNZAHL,

                                  MAXIMALWERT

                                  resident tmpMax;

                                  // DROP Table tmpMax;

                                   

                                  // Create the table with results

                                  Result:

                                  LOAD LEITBEREICH,

                                  Sum(ERWERB_2012)/Applymap('mapMax', 'ERWERB_2012') *10 as SCORE_ERWERB_2012,

                                  Sum(ERWERB_2013)/Applymap('mapMax', 'ERWERB_2013') *10 as SCORE_ERWERB_2013

                                  Resident HEINZE_ROHDATEN

                                  Group by LEITBEREICH;

                                   

                                  Thank you very much for your help!

                                  Florian

                                    • Re: Calculate with Grouped (aggregated) variables during load
                                      Ruben Marin

                                      Hi Florian, how you going with this?

                                       

                                      Just to comment that similar technique can be used to create the script using a variable, like:

                                       

                                      SET vScript ='LEITBEREICH'

                                       

                                      For each vField in 'ERWERB_2012', 'ERWERB_2013'

                                        SET vScriptRow = Sum($(vField))/Applymap('mapMax', '$(vField)') *10 as SCORE_$(vField);

                                        SET vScript =$(vScript),

                                                              $(vScriptRow);

                                      Next


                                      LOAD $(vScript)

                                      ....


                                      *not tested (in particular the SET/LET) but I have done things like this reading the field list from a file

                                        • Re: Calculate with Grouped (aggregated) variables during load
                                          Florian Klostermeier

                                          Hi Ruben,

                                           

                                          sorry for my late reply. I had a flu the last couple days and therefore was not able to think about this issue.

                                          You are absolutely right with the idea. I will edit my syntax accordingly.

                                           

                                          But at the moment, I am thinking about your previous post when you suggested only to save the years:

                                           

                                          For each vYear in ERWERB_2012, ERWERB_2013

                                          tmpMax:

                                          LOAD Subfield('$(vYear)', '_', -1) as KENNZAHL,

                                            Max($(vYear)) as MAXIMALWERT;

                                           

                                          The reason is that I need to calculate the trend for each single row. Unfortunately, it seems that I first have to change the data structure:

                                          https://community.qlik.com/thread/293308

                                           

                                           

                                          Thanks and all the best,

                                          Florian