Variables in Script

[gabyala79]

Hi everyone!

I am trying to define a couple of variables to be used afterwards, and I do not know how to handle this... 

In the first part of the script I define these variables to get information about the lastdate with actuals loaded:

CALENDARACT:
LOAD
DISTINCT Date as DateACT
Resident Fact_Cashouts Where "ACTUAL/TARGET"='ACTUAL';

Let vLastClosingDate = '=Max(DateACT)';
Let vLastClosingDateMonth = '=Num(Month(vLastClosingDate))';
Let vLastClosingDateYear = '=Num(Year(vLastClosingDate))';

I checked the outputs by putting on one page some text boxes with these variables, and it works well.

The problem comes later when trying to use them:

CALENDAR:
LOAD
DISTINCT Date as Date,
Floor(Date) as DateNum,
'$(vLastClosingDateMonth)' as LastMonth,
'$(vLastClosingDateYear)' as LastYear

Resident Fact_Cashouts;

The idea is to get a column filled in with '11' and '2021', but I cannot do it...

Can someone tell me which is my mistake?

Thanks!

[Or]

Your variable is storing the actual string of the formula, not the result of the evaluated expression. When you put it into e.g. a text object it is evaluating the formula on the front end and giving you the result.

If you want to use it this way, I think you can get it done using the Evaluate() function, but I'm not 100% sure.

[Edit] Re-reading this, it looks like you're trying to get the information from the first load into the second load, which is a bit different from what I thought at first. If that's a case, have a look at this thread:

https://community.qlik.com/t5/QlikView-App-Dev/Setting-the-Maximum-value-of-a-field-in-the-script/m-...

[gabyala79]

Thanks,

In fact, I already managed to get the information from the first load and I fail when loading the second one.

I tried:

CALENDAR:
LOAD
DISTINCT Date as Date,
Floor(Date) as DateNum,
Evaluate('$(vLastClosingDate)') as MaxFecha

Resident Fact_Cashouts;

but did not work neither...

[Or]

Perhaps I am missing something here, but how would vLastClosingDate evaluate within the context of your second load? It appears to be based on max() from a field that is only contained within your first load, and you haven't re-defined your variables based on the second load.

The thread I linked explains how to pull the max value of a field based on an existing load, so if you're looking to re-use that max value (or the derived month and year from that value), that's the way to go. Otherwise, I'd need more clarification on what exactly you're trying to achieve.

[gabyala79]

Yes, I want to do what you said: get info from one first load and put it inside the variable, and then use this variable into the second load. In the thread you linked, it is explained how to get the info from the first load and how to define the variable, but not how to use this in a second loop.

When doing it, I'm getting the error saying that the field is not existing.

How can I do it to work with this variable in the second loop?

Thanks again for your help

[Or]

Once you get the variable correctly assigned, you should be able to use it in a simple '$(VariableName)' as SomeFieldName like you already did. The trick is getting the correct value into the variable to begin with. I recommend following the instructions in the thread above and running the script in debug mode to make sure that the value is correctly placed in the variable.

[anthonyj · Accepted solution]

Hi @gabyala79  and @Or ,

You'll need to find the max value of the date field first and then use $ expansion of that variable in the subsequent variables for the values to flow through. 

//Create a table that contains the max value from your distinct dates

temp:
load
date(max(MaxDate)) as MaxDate;
Load
FieldValue('DateACT',Recno()) as MaxDate
AutoGenerate FieldValueCount('DateACT');

//Use the Peek() function to return that maximum date and dollar expansion in the subsequent calls to that variable to resolve.

Let vLastClosingDate = num(peek('MaxDate',0,'temp')) ;
Let vLastClosingDateMonth = Num(Month($(vLastClosingDate)));
Let vLastClosingDateYear = Num(Year($(vLastClosingDate)));

drop table temp;

//Finally load your data

CALENDAR:
LOAD
DISTINCT DateACT as Date,
Floor(DateACT) as DateNum,
'$(vLastClosingDateMonth)' as LastMonth,
'$(vLastClosingDateYear)' as LastYear

Resident CALENDARACT;

drop table CALENDARACT;

I hope this helps.

Thanks

Anthony

[gabyala79]

This worked perfectly.

Thanks!