topic Re: passing variable value in script editor in QlikView

passing variable value in script editor

Tue, 22 Apr 2014 13:04:45 GMT

Hi All,

SQL SELECT

FK_EXT_Bank_Id as PositionBank,"LEBT_Net_Tovr_Loc" as MainBalanceAmount,LEBT_Date

FROM dbo."LED_BAL_TURNOVER"

I used the below logic to get missing data

MinMaxDate:

Load Min(LEBT_Date) as MinDate, Max(LEBT_Date)as MaxDate resident TempTable_Rates;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Drop Table MinMaxDate;

Join (TempTable_Rates)

Load Date(recno()+$(vMinDate)) as LEBT_Date Autogenerate vMaxDate - vMinDate;

Rates:

NoConcatenate Load LEBT_Date,

If( IsNull( MainBalanceAmount ), Peek( MainBalanceAmount ), MainBalanceAmount ) as MainBalanceAmount,
Resident TempTable_Rates

Order By LEBT_Date ; // so that above values can be propagated downwards

Drop Table TempTable_Rates;

But the problem is em not getting all dates for other banks.To get that i added a one more column

( IsNull( PositionBank ), Peek( PositionBank ), PositionBank ) as PositionBank

and added a filter in code

SQL SELECT

FK_EXT_Bank_Id as PositionBank,"LEBT_Net_Tovr_Loc" as MainBalanceAmount,LEBT_Date

FROM dbo."LED_BAL_TURNOVER"

where FK_EXT_Bank_Id = $(v_bank);

now i get correct value on setting the value t0 specific value=1080

but i want to variable value to be chnaged on every selection on bank

how to avchieve this? if i reload em getting it....

Regards,

Prajna

Re: passing variable value in script editor

Roop — Tue, 22 Apr 2014 14:22:34 GMT

How about the For each syntax around the main data ....?

Put your Select statement into a sub-routine called LoadBankData

Sub LoadBankData()

select statement from above goes in here

Endsub;

Enter the following in your script and it will go through Bank1 and Bank2 in turn:

(You could put the 'Bank1','Bank2' into a variable if required)

For each bank in 'Bank1', 'Bank2'

Call LoadBankData()

Next bank

Re: passing variable value in script editor

Tue, 22 Apr 2014 14:27:20 GMT

its new to me can u help me with the syntax?

Regards,

pRAJNA

Re: passing variable value in script editor

Roop — Tue, 22 Apr 2014 15:00:59 GMT

What fields are variable against the bank - I think that perhaps I have given the wrong advice ...... I think that you should be able to load a series of values against any bank and then link those to the bank IDs themselves.

If you look at the Mapping Load syntax (which is identical to the load command, but for 1 field to lookup against) such as Bank ID and another lookup measure.

This can then be used by the ApplyMap('ApplyMapName', Field, DefaultValue)

This would give you the variability that you are seeking.