topic Re: To create dynamic variable in Load Script so that i can create a expression in Load Script in QlikView

To create dynamic variable in Load Script so that i can create a expression in Load Script

Anonymous — Fri, 01 Jun 2018 05:40:39 GMT

Hello,

I was facing an issue with my data model as i have to create everything in the load script. Everything is working as expected but there is one issue that there are two variables created in the Front End like this:

vStartDate=Floor(Date(Max(AddMonths([Fiscal Date],3)),'DD-MMM-YYYY'))

vEndDate= Floor(Date(MonthEnd(Max(AddMonths([Fiscal Date],3))),'DD-MMM-YYYY'))

variable's value depends on the selection of [Fiscal Date]. I Want to d it in Script End as this is the requirement.

Can anybody suggest how to proceed.

Thanks,

Manish Rawat

hic‌ djk‌ lech_miszkiewicz‌ Qlik Community‌

Re: To create dynamic variable in Load Script so that i can create a expression in Load Script

petter — Fri, 01 Jun 2018 06:39:19 GMT

You can define them in the load script by using SET statements like this:

SET vStartDate='=Floor(Date(Max(AddMonths([Fiscal Date],3)),''DD-MMM-YYYY''))';

SET vEndDate='=Floor(Date(MonthEnd(Max(AddMonths([Fiscal Date],3))),''DD-MMM-YYYY''))';

Notice that you have to use two single quotes to get one single quotes inside a string that is delimited by single quotes.

If you don't like the double quoting you could actually write it verbatim but you must then have a space between the two equal characters:

SET vStartDate= =Floor(Date(Max(AddMonths([Fiscal Date],3)),'DD-MMM-YYYY')) ;

SET vEndDate= =Floor(Date(MonthEnd(Max(AddMonths([Fiscal Date],3))),'DD-MMM-YYYY')) ;

Re: To create dynamic variable in Load Script so that i can create a expression in Load Script

Anonymous — Fri, 01 Jun 2018 07:15:15 GMT

Hey Petter,

Thank you for your reply.

How do you want me to implement this as if i declare those two variable Using SET and with using peek, then i'll be getting either Max Value or Min Value depending upon the parameter we give in Peek (0 or -1).

My Requirement:

Existing:

If user Selects Year=2018 and Month=Apr then vStart= 4/1/2018 (Num Converted to date)

and similarly vEnd=4/30/2018 . It all depends on the selection.

As it should be:

it all should happen in Load Script.

Kindly provide me the workaround to do as you suggested.

Thanks in Adv.

Re: To create dynamic variable in Load Script so that i can create a expression in Load Script

petter — Fri, 01 Jun 2018 08:28:02 GMT

If you want to get the max value of [Fiscal Date] while running the load script then you can use a Peek. Then it is not a selection in Qlik terms....

MAX_FISCAL: LOAD Max([Fiscal Date]) AS MaxFiscalDate RESIDENT <the-table-containing-Fiscal-Date>;


vStartDate=Floor(Date(AddMonths(Peek(MaxFiscalDate),3),'DD-MMM-YYYY')) ;
vEndDate=Floor(Date(MonthEnd(AddMonths(Peek(MaxFiscalDate),3),'DD-MMM-YYYY')) ;

DROP TABBLE MAX_FISCAL;

Re: To create dynamic variable in Load Script so that i can create a expression in Load Script

petter — Mon, 04 Jun 2018 13:08:28 GMT

Did this work for you?

Re: To create dynamic variable in Load Script so that i can create a expression in Load Script

Anonymous — Tue, 05 Jun 2018 06:44:05 GMT

Hey Petter,

It didn't worked but i somehow made a logic on the back end to summaries the Date field with the All the data (Year Month) and made a flag out of it.

It is working fine for me now.

Thanks for your support. Really appreciate it.

Re: To create dynamic variable in Load Script so that i can create a expression in Load Script

petter — Tue, 05 Jun 2018 06:46:12 GMT

You're welcome. Please mark the question as answered to close the thread.