How to use if and match in qliksense.

[qlikuser22]

Hello,

I am a new user of QlikSense, and I am having difficulty with doing a certain task. I'm sure it's fairly simple, but I don't know how to go about this. 

I have a table of unique ID and their currency and time details. Few fields are NULL in currency. If I replace all NULL values into INR currency then I am having false data.

I want to match match all unique IDs with same currency and then replace the NULL with INR currency. 

Example data:

So here for ID 101 we have the currency USD, I want that to be placed in all NULL values for ID 101.

Same for ID 102 it should be EUR and the rest of the NULL (ID 103,105)  should be replaced by INR if doesn't have any matching  currency for that ID. 

Thanks in advance! 

[stevejoyce · Accepted solution]

See below on how to clean this up in load script.

//load source data
data:
load * inline [
ID, CURRENCY, TIME
101, NULL, 2021-01-03
101, USD, 2021-02-03
101, NULL, 2021-04-03
102, EUR, 2021-09-04
102, EUR, 2021-09-09
103, NULL, 2021-09-08
105, NULL, 2021-09-07
]
;

rename field CURRENCY to currency_source;

//get all ID with valid currencies
currency:
load distinct
ID,
currency_source as CURRENCY,
ID as ID_hasvalidcurrency
resident data
where not(len(currency_source)=0 or currency_source='NULL')
;

//concatenate IDs without a valid currency
concatenate(currency)
load distinct
ID,
'NULL' as CURRENCY
resident data
where not exists(ID_hasvalidcurrency, ID);

//join result back to data table
left join (data)
load * resident currency;

//cleanup
drop table currency;
drop fields currency_source, ID_hasvalidcurrency;

exit script

[chris_djih]

Assuming your base table is called "Table1" this woul be my solution:

Currency_MAP:
Mapping load
ID,
Currency
resident Table1
where len(trim(Currency))>0 //
;

FinalTable:
Load *, //load the complete old table into tihs new one
	ApplyMap('Currency_MAP',ID,'INR')	as new_Currency 
Resident Table1;

drop table Table1; //delete the old table
drop field Currency; //delete old currency field

rename field new_Currency to Currency; //optioinal if you need the field in the old name

the idea is:
1. collect all distinct Currency for ID in a mapping table.
2. apply the map and write 'INR' as default, if there was no match (Currency was null)
3. cleanup fields and tables

[qlikuser22]

Thanks it worked perfect!