Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smic
Partner - Contributor II
Partner - Contributor II

Add date and datas with "while" during load

Hello,

Here is the problem that I cannot solve ... I need to load data from an excel file that contains conversion rates for currencies. Here is what I have as input:

CurrencyDate_ENDRates
AUD31/10/20181.4068
AUD30/11/20181.3799
AUD31/12/20181.3893
AUD31/01/20191.4005


Here is what I would like to get:

CurrencyDate_ENDRates
AUD31/10/20181.4068
AUD01/11/20181.3799
AUD02/11/20181.3799
AUD03/11/20181.3799
AUD04/11/20181.3799
AUD05/11/20181.3799
.........
AUD30/11/20181.3799
AUD01/12/20181.3893
AUD02/12/20181.3893
AUD03/12/20181.3893
.........
AUD31/12/20181.3893

 

I must "create" the lines for the missing days, keeping the rates entered in the line containing the "date_end". So each time I have a time interval to enter, keeping the data from the "max" line of this interval ...

I carried out several tests with "while" but did not arrive at the good result ... If someone can help me please?

 

Regards

1 Solution

Accepted Solutions
jyothish8807
Master II
Master II

Hi Smic,

Since you have the same rates for the entire month it should not be an issue. The moment  you do a left join all the rates will be replicated to each dates from your second table.

May be if you can share what have you build may help me in guiding you in a better way 🙂

 

Br,

KC

 

Best Regards,
KC

View solution in original post

3 Replies
jyothish8807
Master II
Master II

Hi Smic,

May be the below approach help:

Create a month year filed in you current table and create a separate table (or master calendar) with  Month year and date and connect or left join the month Year field:

Load

Date(Date_END,'MM YYYY') as MonthYear,

*

from <>;

leftjoin

Load

MonthYear, Date (All the dates in the month)

from <Master calander>

 

Best Regards,
KC
smic
Partner - Contributor II
Partner - Contributor II
Author

Thanks you for reply,

 

I managed to fill in the missing dates. But how can I browse my tables and feed the "Rates" field if the date field is lower than the date field of another table please?

 

Regards

jyothish8807
Master II
Master II

Hi Smic,

Since you have the same rates for the entire month it should not be an issue. The moment  you do a left join all the rates will be replicated to each dates from your second table.

May be if you can share what have you build may help me in guiding you in a better way 🙂

 

Br,

KC

 

Best Regards,
KC