Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have table like below mentioned. I need to create incremental load based on Month.
Please some one help me how to create Incremental Load
Brand Name | customer | January | February | March | April | May | June | July | August | September | October | November | December |
ABC | Godrej | 0 | 1234 | ||||||||||
XYZ | Samsung | 0 | 5678 |
Hi Siva,
Okay thanks that makes sense, I've attached a QVF file with the script I usually use for all my Incremental loads.
I adjusted the script with the data fields you mentioned in this post. I also attached the dummy data file I used with this extract.
On the second tab you'll just need to change the data source locations and then run the reload, it will then create the initial data QVD and from then on any reloads will add data to the existing QVD.
I've made comments on the script if you need anything for reference. Hope it helps, let me know how it goes.
Please let me know here How I can identify Max (Monthname) since I don't have year here. I need to Ignore columns which are not having data
You can use the crosstable function to create a month field and then limit the data to months where data is available like below. If you want a variable with max month you can then use Max(MonthNo). Hope this helps
Temp:
CrossTable(Month, Value, 2)
LOAD [Brand Name],
customer,
January,
February,
March,
April,
May,
June,
July,
August,
September,
October,
November,
December
FROM
[*****]
(ooxml, embedded labels, table is Sheet1);
Fact:
load *,
Num(Month(Date#(Month, 'MMMM'))) AS MonthNo
Resident Temp
where not isnull(Value);
drop table Temp;
Hi Stefan,
Thanks for your reply but please could you tell me where I can insert Max(MonthNo) in above script?
I want reload only updated Month records from above file. For Example Jan, Feb already loaded but March data is not loaded so I want load only March data
Please let me know how it is possible. Much appreciated you help
Regards,
Siva
Hi Siva,
I made some changes to the script, the first section creates the vMaxMonth variable and the second section shows a example of how you can use that variable to load only the max month.
I hope I'm understanding correctly what you want to achieve :-), if below is not what your looking for let me know and we can brainstorm a bit.
// Below creates the vMaxMonth variable
Temp:
CrossTable(Month, Value, 2)
LOAD [Brand Name],
customer,
January,
February,
March,
April,
May,
June,
July,
August,
September,
October,
November,
December
FROM
[******************]
(ooxml, embedded labels, table is Sheet1);
Max_Month:
load
Text(Date(max(Date#(Month, 'MMMM')),'MMMM')) as MaxMonth
Resident Temp
where not isnull(Value) or Len(Value) > 0;
let vMaxMonth = peek('MaxMonth',-1);
drop table Temp, Max_Month;
// ************ END *****************************************
// Example Fact table where you can use the vMaxMonth variable
Fact:
load [Brand Name],
customer,
$(vMaxMonth)
FROM
[******************]
(ooxml, embedded labels, table is Sheet1);
Hi Steafan,
Thanks for response. I would like to achieve like below mentioned. while appending Old data with New data. I am getting some loading issue. Please advice me how I can achieve it
Increment:
CrossTable(Month,Value,7)
LOAD
"Sales Zone",
"Sales Division",
"Ship To State Code",
"Ship To Customer ID" AS CustomerID,
"Ship To Customer Name",
"Brand Name",
"Ship To Customer Name+Ship To Customer ID+Brand Name",
January,
February,
March,
April,
May,
June,
July,
August,
September,
October,
November,
December
FROM [lib://AttachedFiles/2021 Flash Forecasts by Month Upload - EXAMPLE.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Upload);
store Increment into $(vQVDPath_Extract)/Incremental.qvd;
Max_Month:
load
Text(Date(max(Date#(Month, 'MMMM')),'MMMM')) as MaxMonth
Resident Increment
where not isnull(Value) or Len(Value) > 0;
let vMaxMonth = peek('MaxMonth',-1);
drop table Increment, Max_Month;
Fact:
Load "Sales Zone",
"Sales Division",
"Ship To State Code",
"Ship To Customer ID" AS CustomerID,
"Ship To Customer Name",
"Brand Name",
"Ship To Customer Name+Ship To Customer ID+Brand Name",
$(vMaxMonth) as Month,
'0' as Value
FROM [lib://AttachedFiles/2021 Flash Forecasts by Month Upload - EXAMPLE.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Upload)
where Month='$(vMaxMonth)';
Concatenate
LOAD
"Sales Zone",
"Sales Division",
"Ship To State Code",
CustomerID,
"Ship To Customer Name",
"Brand Name",
"Ship To Customer Name+Ship To Customer ID+Brand Name",
"Month",
Value
FROM [lib://QlikDataManagement/EDW_Data/Extract QVD/Incremental.qvd]
(qvd);
Hi Siva,
Okay thanks that makes sense, I've attached a QVF file with the script I usually use for all my Incremental loads.
I adjusted the script with the data fields you mentioned in this post. I also attached the dummy data file I used with this extract.
On the second tab you'll just need to change the data source locations and then run the reload, it will then create the initial data QVD and from then on any reloads will add data to the existing QVD.
I've made comments on the script if you need anything for reference. Hope it helps, let me know how it goes.
Hi Stefan,
in your incremental load.qvf, the QVD locations for
let vIncremental_Data_Location = ...
how should I get the data if my data are real-time from DB?