Qlik Sense: Use of loaded measures in script

[demonioazul]

Hi there!

I need help with the following issue.

To make things simple, I have a similar script as the one here:

LOAD

    BusinessUnit,

    YTD,

    Pipeline

FROM xxx....;

but to create a stacked bar chart I need a new measure dependent on the ones I am loading first, so I need the following to work:

LOAD

    BusinessUnit,

    YTD,

    Pipeline,

    YTD - Pipeline as RunRate

FROM xxx....;

The data load of this script is successful with no errors, but RunRate has no values

I have tried to load variable names and their formulae from a script, which outputs the same result with only an additional step that provides no added value.

If I create a table visualization using the either script and in the measure fx field I use YTD-Pipeline (not the RunRate measure) it shows the correct values...

If I create a measure defining it in the Measures tab, it also works fine. However, I am not able to use it to create my stacked bar chart because defined like that it does calculate the values but it does not belong to any table...

I also tried a RESIDENT LOAD trying to add RunRate as follows:

TempTable:

LOAD

    BusinessUnit,

    YTD,

    Pipeline

FROM xxx....;

FinalTable:

LOAD

    BusinessUnit,

    YTD,

    Pipeline,

    YTD-Pipeline as RunRate

RESIDENT TempTable;

But that gives the very same results: no values in RunRate...

The goal is to be able to re-use measures in the load script.

Does anyone have a hint? It will be much appreciated. Thanks!

[kaushiknsolanki]

Hi,

Not sure but just give a try with below script.

LOAD

    BusinessUnit,

    YTD,

    Pipeline,

    Num(YTD) - Num(Pipeline) as RunRate

FROM xxx....;

Regards,

Kaushik Solanki

[demonioazul]

Thanks, kaushik.solanki‌... it did not work....

No load script errors, but when I try to use the plain RunRate it shows only dashes (meaning the values of RunRate are null) and if I use sum(RunRate) all values are zero, which is wrong since YTD and Pipeline not necessarily are always the same to produce YTD-Pipeline = 0....

[kaushiknsolanki]

Demian,

I can feel it is happening because one of your value either YTD or Pipeline is coming as text and thats the reason why when you use the maths on text result will be null, which you are getting in RunRate.

Check on that.

Also share some sample data with us to investigate more.

Regards,

Kaushik Solanki

[demonioazul]

Hi kaushik.solanki‌,

No, no data is text.

In "real life" I will get the Pipeline data from one source and the YTD from another, that is why I am using two tabs in the Excel file and concatenating the tables in the LOAD script and using a RESIDENT load in order to be able to transform data, as recommended by the Help webpage.

Please see attached QVF and XLSX files.

Thanks for your help!

~Demian

[kaushiknsolanki · Accepted solution]

Hi,

It is because when you concatenate it wont work the way you want.

Try below script.

Map:

Mapping LOAD [BU],

  Sum([Pipeline]) as [Pipeline]

FROM [lib://Data1 (lenovo-pc_lenovo)/Reuse Measures Test Data.xlsx]

(ooxml, embedded labels, table is Opportunities)

Group by BU;

Data:

Load *,YTD-[Pipeline] as RunRate;

LOAD

    BU,

    ApplyMap('Map',BU) as [Pipeline],

    Sum(YTD) as YTD

FROM [lib://Data1 (lenovo-pc_lenovo)/Reuse Measures Test Data.xlsx]

(ooxml, embedded labels, table is Actuals)Group by BU;

Regards,

Kaushik Solanki

[demonioazul]

AWESOME!!!!

Thanks so much, kaushik.solanki‌!!!!!!!!!!!!!

I was indeed so faaaaaaaaar from the right answer to this issue!

[kaushiknsolanki]

Anytime Demian.

Feel free to post any of your queries on community.

Regards,

Kaushik Solanki

[demonioazul]

kaushik.solanki‌...

I have an issue now... I did post here some simple sample data because I thought I could learn and edit the script once I got help.

Your solution is very useful but if I have more fields and I still need to "merge" those tables' fields in one to make calculations on the loaded measures, then I can't do it.  

I would like to post the new data file and the new QVF but this chat/forum does not allow me to...

[demonioazul]

Do I post another question so I can get further help?