Fill the missing dates

[qlikuser22]

Hi,

I have a table with date column starting from Jan2023. I want to modify my table with all the columns that is starting from June 2022.

Table:

Date,

source_currency,

destination_currency,

rate

from abc.xslx;

I need the date column to be starting from June 1, 2022 till Dec 31, 2022 with the following values,

I don't have the data from June to December, so I want to do this manually as a temporary fix. 

Need some help in doing this in qlik script.

Thanks in advance!

[AronC]

Do I understand correctly? You have data from jan-2023 in your app.  You want to add data from an excelfile with data from 2022. Correct?

If that is the case then you just concatenate the new table (2022-data)  to your original (2023). Make sure the columnnames are the same.

Concatenate (OrinalTableName2023data) 

Load

Date,

source_currency,

destination_currency,

rate

from abc.xslx;

(where abc.xlsx contains your 2022-data you want to add)

[qlikuser22]

Hi,

thanks for the reply. 

I can make it clear. I want to add the dates manually with the values from Jun 2022 to Dec 2022 in the below table. 

Table:

Date,

source_currency,

destination_currency,

rate

from abc.xslx;

[AronC]

I still dont understand what you are struggling with. Is it loading the abc.xlsx-file into Qlik? When you write add dates manually, what do you mean by manally? What does your data look like besides these 2022 data. Can you please take a snapshot of the datamodel?

[MayilVahanan · Accepted solution]

Hi

Try like below

Temp:
LOAD *, '1' as source INLINE [
Date, source_Currency, destination_currency, rate
01-06-2022, EUR, INR, 89.42
01-06-2022, EUR, USD, 1.09
];
Let vStartDate = MakeDate(2022,06,01)-1;
Let vEndDate = floor(MakeDate(2022,12,31));

Final:
Load Distinct source_Currency, destination_currency, rate Resident Temp;
Join
Load Date($(vStartDate)+IterNo()) as Date AutoGenerate 1 While ($(vStartDate)+IterNo()) <= $(vEndDate);

DROP Table Temp;

[qlikuser22]

thanks a lot. Working as expected.