Flag in script

[#1]

Hi

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.

[migueldelval]

Hi Veronica,

Could you attach a sample data?

Regards

Miguel del Valle

[#3]

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

[migueldelval]

Hi Veronica,

Your qvw file it´s empty, check please.

Regards

Miguel del Valle

[#5]

Hi Miguel

Find attached.

Regards

[henrikalmen]

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.

[henrikalmen]

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.

[#8]

Hi Henrik

Find attached script error received when using your suggestion.

Thanks

[#9]

Hi Henrik

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

Regards

Veronica

[henrikalmen · Accepted solution]

Good to hear!

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