Calculate with Grouped (aggregated) variables during load

[florian_kloster]

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.

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

[rubenmarin]

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*

[florian_kloster]

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

[rubenmarin]

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

[florian_kloster]

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

[florian_kloster]

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

[rubenmarin]

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;

[florian_kloster]

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

[rubenmarin]

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

[florian_kloster]

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