topic Re: Flag in script in QlikView

Flag in script

Wed, 25 Nov 2020 16:16:04 GMT

I have imported two spreadsheets and created a single table in Qlikview. I would like to add a flag field that would indicated where three of the fields are populated with values ie. it should reflect "1" if there is a value in all three fields and "0" if there is a missing value on any of the fields.

The dilemma I have is that I have left joined each table and created one table so the fields are not recognised when using the flag.

I am using the following formula in the load script.

If (Dom_InflRate<>null() and For_InflRate<>null() and XRate<>null(), Flag=1, Flag=0)

I have attached the screenshot of the table.

Please help.

Re: Flag in script

migueldelval — Mon, 26 Jun 2017 08:26:44 GMT

Hi Veronica,

Could you attach a sample data?

Regards

Miguel del Valle

Re: Flag in script

Mon, 26 Jun 2017 08:40:26 GMT

Hi Miguel

Thank you for the prompt response.

I have attached the model. Could you also assist with the data tab where I would like to conduct a check on the data available for the

Exchange Rates , Domestic Inflation and Foreign Inflation (tables on the right).

I would like the data field to automatically select data available for common dates for the above three fields when a CurrencyPair is chosen.

Trust that this provides clarification.

Regards

Veronica

Re: Flag in script

migueldelval — Mon, 26 Jun 2017 08:47:32 GMT

Hi Veronica,

Your qvw file it´s empty, check please.

Regards

Miguel del Valle

Re: Flag in script

Mon, 26 Jun 2017 08:54:20 GMT

Hi Miguel

Find attached.

Regards

Re: Flag in script

henrikalmen — Mon, 26 Jun 2017 08:57:24 GMT

I'm not quite sure what you mean, but try adding this in your script:

left join(ExchangeRate)

load *,

If (isNull(Dom_InflRate) or isNull(For_InflRate) or isNull(XRate), 0, 1) as Flag

Resident ExchangeRate

;

That should give you a "Flag" field that you can use in objects.

Re: Flag in script

henrikalmen — Mon, 26 Jun 2017 09:01:21 GMT

I see that you have now attached a sample file. Remove the attempt att creating the table NullValues in the top of the tab ExchageRates, and remove the statement "If (ISNull(XRate),Flag=1,Flag=0)" in creation of the table ExchangeRates.

Then add my suggestion after the creation of table ExchangeRates. I believe the result will be what you want.

Re: Flag in script

Mon, 26 Jun 2017 09:13:42 GMT

Hi Henrik

Find attached script error received when using your suggestion.

Thanks

Re: Flag in script

Mon, 26 Jun 2017 09:20:58 GMT

Hi Henrik

Apologies, I have rerun and it works. Thanks , much appreciated.

Regards

Veronica

Re: Flag in script

henrikalmen — Mon, 26 Jun 2017 09:34:58 GMT

Good to hear!

Please mark my answer as correct so that this topic is closed.

Re: Flag in script

Mon, 26 Jun 2017 10:34:07 GMT

HI Henrik

Could you also assist with the minimum date filters. As you will see, the data starts at different dates for the three fields of interest for each currencypair. I would like to create a minimum date filter for each field.

Thanks

Re: Flag in script

henrikalmen — Mon, 26 Jun 2017 11:59:43 GMT

I'm not sure what it is you want but perhaps something like this:

MinimumDate: load CurrencyPair, min(Date) as mindate Resident ExchangeRates group by CurrencyPair;

Maybe this will at least send you in the right direction.

Re: Flag in script

Mon, 26 Jun 2017 12:35:40 GMT

Hi Henrik

Thank you ,

I am looking for something like this :

Minimum Date of Foreign Inflation(Field called For_InflRate of a currency pair with a value ie. the minimum start date with a value.

The same for Exchange Rate(field called XRate) and Domestic Inflation(field called Dom_InflRate).

Regards

Re: Flag in script

henrikalmen — Mon, 26 Jun 2017 12:47:34 GMT

You'll have to check if CurrencyPair has a value, for example by using len(trim(CurrencyPair))>0 in a where-clause. And instead of load CurrencyPair in my examle you should load min(Date) over the dimension For_InflRate and the other fields. Depending on what you want to do with that you could set a flag in your table to indicate what row has the minimum value, or you could create a table with those values (if so, you should look up how to use Concat() in your load script.