topic Re: Qlik Sense: Use of loaded measures in script in App Development

Qlik Sense: Use of loaded measures in script

demonioazul — Thu, 23 Feb 2017 17:17:50 GMT

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!

Re: Qlik Sense: Use of loaded measures in script

kaushiknsolanki — Thu, 23 Feb 2017 17:47:31 GMT

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

Re: Qlik Sense: Use of loaded measures in script

demonioazul — Thu, 23 Feb 2017 18:08:53 GMT

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....

Re: Qlik Sense: Use of loaded measures in script

kaushiknsolanki — Thu, 23 Feb 2017 18:15:52 GMT

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

Re: Qlik Sense: Use of loaded measures in script

demonioazul — Thu, 23 Feb 2017 19:12:56 GMT

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

Re: Qlik Sense: Use of loaded measures in script

kaushiknsolanki — Thu, 23 Feb 2017 19:24:15 GMT

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

Re: Qlik Sense: Use of loaded measures in script

demonioazul — Thu, 23 Feb 2017 19:36:48 GMT

AWESOME!!!!

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

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

Re: Qlik Sense: Use of loaded measures in script

kaushiknsolanki — Thu, 23 Feb 2017 19:39:10 GMT

Anytime Demian.

Feel free to post any of your queries on community.

Regards,

Kaushik Solanki

Re: Qlik Sense: Use of loaded measures in script

demonioazul — Mon, 27 Feb 2017 19:53:53 GMT

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...

Re: Qlik Sense: Use of loaded measures in script

demonioazul — Mon, 27 Feb 2017 21:01:39 GMT

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

Re: Qlik Sense: Use of loaded measures in script

demonioazul — Mon, 27 Feb 2017 22:45:13 GMT

Hi kaushik.solanki‌,

I noticed that from my Community profile Inbox it is not possible to reply and attach files.

As explained above, I thought it would be easy to just extrapolate the hint to a more complicated data load... but I could not do that...

I get the data from different sources (for this purpose I put the data in different tabs in the data file) and try to merge them in one app so I can make comparisons, visualizations and tables...( but it has proven more complicated than expected... I've using Qlik Sense almost two years now and this challenge is the hardest I ever had so far...)

Then, after merging those tables, I need to calculate also an offset using the variables to generate a crosstable with two dimensions and one measure in order to be able to plot a stacked bar chart, Something like this:

which, by the way, I am able to create by having the table generated in the attached QVF and calculating manually the offsets...

But I want to have everything automated...

Or maybe my approach is wrong?

If I can accomplish to load a table similar to the one shown in the Sample - TAR.xlsx file, then I would be able to generate the table with no issues... but so far I haven't been able to find the right way...

I can generate each Business Unit (BU) or Business Segment (BS) chart separately by manually creating the tables for them, but that is not the case... I need Qlik Sense to do it for me...

I want that chart to show dynamically the information for any BU or any BS I choose...

Re: Qlik Sense: Use of loaded measures in script

kaushiknsolanki — Tue, 28 Feb 2017 15:00:52 GMT

Hi Demian,

I will sure go through the problem statement and the sample data. I request you to create a new post so that it can help others too.

Once you do that, please reply here with the post link, so that i can reply to that.

Regards,

Kaushik Solanki