Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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)
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;
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?
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;
thanks a lot. Working as expected.