Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Sameer9585
Creator II
Creator II

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
miskinmaz
Creator III
Creator III

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
Contributor II
Contributor II

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
Creator II
Creator II
Author

Hi HariHaran,

I request you to please refer to the output file also

Regards,

Sameer