Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Sameer9585
Contributor

Filling Dates and Rates

Hi,

I have a dataset where it contains Currency,Start_Date,End_Date and Rate.Now I need a feild of complete dates based on Start and End Date of both the currencies.Now the rate of the date which are not present in the dataset are to be set with both curency types and the rate value should be the previous ones.I provide the dataset in it sheet 7  is the dataset.

I need the output as the file below.

Thanks in advance, 

3 Replies
Highlighted
miskinmaz
Contributor II

Re: Filling Dates and Rates

Hi,
You need to skip the first part of the scripting from below link since you already have an end date.

 

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/create-dat...

Hariharan_S
New Contributor II

Re: Filling Dates and Rates

Try this code -

 

T1:
Load * Inline
[
Currency,Start_Date,End_Date,exrate
USD_INR,04/01/2019,04/05/2019,75
USD_INR,04/08/2019,04/14/2019,74
USD_INR,04/16/2019,04/21/2019,73
EUR_INR,04/01/2019,04/04/2019,86
EUR_INR,04/07/2019,04/14/2019,87
EUR_INR,04/16/2019,04/25/2019,85
];


for i = 0 to NoOfRows('T1')-1
Let vMinDate = peek('Start_Date',i,'T1');
Let vMaxDate = peek('End_Date',i,'T1');
Let vCurrency = peek('Currency',i,'T1');
Let vexrate =peek('exrate',i,'T1');

Temp:
Load
    date('$(vMinDate)'+rowno()-1) as tempdate
AutoGenerate
date(vMaxDate)-date(vMinDate)+1;

Final:
Load
'$(vCurrency)' as Current,'$(vexrate)' as exr,tempdate Resident Temp;

drop table Temp;

Next I;

Sameer9585
Contributor

Re: Filling Dates and Rates

Hi HariHaran,

I request you to please refer to the output file also

Regards,

Sameer