How to populate / replace NULL rows with values in the same field, of the same day?

[dominicmazvimavi]

Hi everyone. 
 TABLE1

I have the above table. I would like to populate the rows of EXCHANGE field, where the value is NULL, with identical values of the populated non - null rows of the same day, in my QlikView script. how do i go about it?

Note: EXCHANGE field, is an exchange rate field, therefore, the exchange rate for each each day should be the same

[Kushal_Chawda]

@dominicmazvimavi  may be try below

Data:
Load Date,
     Amount, 
     if(len(Trim(Exchange))=0,Null(),Exchange) as Exchange;
FROM Source;

left Join(Data)
Load Date,
     sum(DISTINCT Exchange) as new_exchange
Resident Data
Group by Date;

[dominicmazvimavi]

Hi thank you for the response, but I am still getting 0 in the exchange in the final output, and not a real figure of the exchange rate. what else can I do ? Further, does the trim() function not remove some of my data (such as that of exchange rate that is equal to 0) ?

[wcilliers · Accepted solution]

Hi @dominicmazvimavi ,

Try loading your exchange rate table separately without the amount.

IE:

Rates:
Load Distinct
Date,
Exchange
From Source Where IsNull(Exchange) = 0;

Then Join it with your amounts table.

[vinieme12]

create a mapping table, this way you could avoid doing another load for creating any calculated fields using the exhange value

MapExchange:

Mapping Load Date,Exchange

From DatasourceTable

Where len(Exchange);

Fact:

Load Date

,Amount

, Applymap('MapExchange',Date,1) as Exchange

, Amount * Applymap('MapExchange',Date,1) as Amount_XYZ_Currency

From DatasourceTable;

[Kushal_Chawda]

@dominicmazvimavi  not sure if you are doing exactly as I mentioned or not but it should work. Probably check if your null values are actually null or it is text 'NULL', seems like it is text null so you can try below

Data:
Load Date,
     Amount, 
     if(lower(Trim(Exchange))='null',Null(),Exchange) as Exchange;
FROM Source;

left Join(Data)
Load Date,
     sum(DISTINCT Exchange) as new_exchange
Resident Data
Group by Date;

[dominicmazvimavi]

Thank you !
This worked finally