Incremental Load based on Month

[siva_boggarapu]

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

[siva_boggarapu]

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]

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]

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]

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]

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 · Accepted solution]

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]

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?