This is another example with the code:
/* Comments in the script*/
// TempDATE:
// LOAD Date($(ReportBegin)+Iterno()-1) as DATE
// AutoGenerate(1) //this is generating just 1 row, you need all dates in between. There is no 1 answer how to do it, I'll show one.
// While $(ReportBegin)+Iterno()-1 <= $(ReportEnd);
/* this is the data you already have and want to apply FX rate to*/
DATA:
LOAD
DATE(Date#(TransactionDate,'D/M/YYYY')) AS TransactionDate //using date and Date#. Date# convert text to date. Date convert to date format as per Main
;
LOAD * INLINE [
TransactionDate
15/1/2024
5/3/2024
]
;
//from this table we only want distinct dates, or you can use it as your calendar if you add other loads with derivated fields.
//in my latest development I preffer create generated calendars from a field.
//Read this: https://community.qlik.com/t5/Qlik-Sense-Documents/Derived-Calendar-Fields/ta-p/1485683
ALL_DATES_FROM_MIN_TO_MAX:
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS DATE
,maxdate
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('TransactionDate', recno()))-1 as mindate,
max(FieldValue('TransactionDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('TransactionDate');
/*
Read this about the calendar:
https://qlikviewcookbook.com/2015/05/better-calendar-scripts/
*/
//why are you doing 2 times the select, not needed, just get your DATE, CURRENCY, RATE and UNIT in one SQL SELECT.
// Join //always indicate where you join to, not mandatory but always good to know
// TempCURRENCIES:
// LOAD
// Distinct C_CURRENCY;//here you are selcting currency
// SQL
// C_CURRENCY
// FROM DB;
// Left Join //always indicate where you join to, not mandatory but always good to know
// TempRATE: //why naming the table if joining, not needed
// LOAD
// DATE,
// C_CURRENCY,//here you have the same but more fields, just do it in one step.
// RATE,
// UNIT;
// SQL
// SELECT
// DATE,
// C_CURRENCY,
// RATE,
// UNIT
// FROM DB;
TempRATE:
LOAD
DATE(Date#(DATE,'D/M/YYYY')) AS DATE,
C_CURRENCY,
Num(RATE) as RATE,//make sure your numbers are always formated as numbers. If it text and you use them in math expression will give null as result
UNIT
//when working with dates, the format is the source number 1 of issues, make sure you format always the same all dates
//consider also use always Floor(DATE) to keep it as numerical and with no decimals (minutes, sec if not needed).
//keep dates field used for Key or as Date or as Floor (DATE) meaning numerical. I preffer user Floor(DATE) as KEY_DATE.
//COnsider use alaways Autonumber in all your KEYs field once your model is done
//pay attention if you use incremental load, autonumber will not work.
//you can use Autonumber 'KEY_*' on the end of your script in the dashboard.
;
LOAD * INLINE [
DATE,C_CURRENCY,RATE,UNIT
15/1/2024, USD, 1.01, 1
20/1/2024, USD, 1.05, 1
1/3/2024, USD, 1.07, 1
20/1/2024, EUR, 1.02, 1
25/2/2024, EUR, 1.06, 1
]
;
/* first creating cartesian product for my dates table, preffer this rather than read 2 times SQL*/
outer join (ALL_DATES_FROM_MIN_TO_MAX)
LOAD Distinct
C_CURRENCY
RESIDENT
TempRATE
;
/* now adding back with all datest for each currency*/
outer join (TempRATE)
LOAD Distinct //pay attention to join distinct, sometimes its doing distinct in the table yu join to. Use with caution. For this purpose we are interested in distinct
DATE,
C_CURRENCY
RESIDENT
ALL_DATES_FROM_MIN_TO_MAX
;
DROP TABLE ALL_DATES_FROM_MIN_TO_MAX;
/* whe I work with populating the data from previous, first I like to create a table ordered already as I need
to avoid any issue with my peek function. Also, I preffer Peek rather that Previous because Peek you can use in the load with the field
you are creating in the load, while the Previous must be the field already created previously in your resident load*/
TempRATE_02:
NoConcatenate
LOAD
DATE,
C_CURRENCY,
RATE,
UNIT
RESIDENT
TempRATE
Order by //the order of fields is important
C_CURRENCY,
DATE Asc
;
Drop table TempRATE;
NoConcatenate
RATE:
Load
DATE,
C_CURRENCY,//this field is already populated from the join
IF(
not IsNull(RATE)
,RATE
, IF(Peek(C_CURRENCY)=C_CURRENCY //your condition is to check if the currency is still the same, if you have more unique fields, add them also
, Peek(RATE)
,null()
)
) as RATE,
IF(
not IsNull(UNIT)
,UNIT
, IF(Peek(C_CURRENCY)=C_CURRENCY
, Peek(UNIT)
,null()
)
) as UNIT
// If(IsNull(C_CURRENCY), if(Match(C_CURRENCY, Previous(C_CURRENCY)), Peek(C_CURRENCY), C_CURRENCY) as C_CURRENCY,
// If(IsNull(RATE), if(Match(RATE, Previous(RATE)), Peek(RATE), RATE) as RATE,//your main field is currency, so you must check prev currency always
// If(IsNull(UNIT), if(Match(UNIT, Previous(UNIT)), Peek(UNIT), UNIT) as UNIT
Resident TempRATE_02
;
Drop Table TempRATE_02;
