Skip to main content

New to Qlik Sense

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

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
siva_boggarapu
Creator II
Creator II

Incremental Load based on Month

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 NamecustomerJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
ABCGodrej01234          
XYZSamsung05678          
Labels (1)
1 Solution

Accepted Solutions
stefan152
Contributor II
Contributor II

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.

 

View solution in original post

7 Replies
siva_boggarapu
Creator II
Creator II
Author

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 

stefan152
Contributor II
Contributor II

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;

 

siva_boggarapu
Creator II
Creator II
Author

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

stefan152
Contributor II
Contributor II

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);

siva_boggarapu
Creator II
Creator II
Author

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);

stefan152
Contributor II
Contributor II

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.

 

hafiyuddin_azhad
Contributor II
Contributor II

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?