Issue with incremental load using variable

[#1]

Hi

I am trying to do incremental load with the following script. But gives the error message saying that "Field not found - <MaxYear>". As per me everything looks to be correct only. Can some please tell me whats wrong with this script.

ExistingTable:
LOAD Field1
     ,Field2
     ,Year
     Month
..\App\Existing.qvd (qvd);
Temp:
Load Max(Year) AS MaxYear,Max(Month) AS MaxMonth Resident ExistingTable;
Concatenate(ExistingTable)
LOAD Field1 
     ,Field2
     ,Year
     ,Month    
FROM
..\App\Data.xls
(ooxml, embedded labels, table is Sheet1)
Where Year > $(vMaxYear)
Where Month > $(vMaxMonth);

Thanks

Attitude

[#2]

Hi

I think I need to pass the variable like below. Am I right?

Where Year > '$(vMaxYear)'
Where Month > '$(vMaxMonth)';

Thanks

Attitude

[#3]

Hi All

Below is the change which I did. I hope this way incremental should be working fine.

What I am trying to do here is I just want to append only those rows where the Year/Month > Existing(Max(Year)/Max(Month)). Please correct me if I am wrong any where here.

ExistingTable:
LOAD Field1
     ,Field2
     ,Year
     Month
..\App\Existing.qvd (qvd);


Temp:
Load Max(Year) AS MaxYear,Max(Month) AS MaxMonth Resident ExistingTable;


Concatenate(ExistingTable)


LOAD Field1 
     ,Field2
     ,Year
     ,Month    
FROM
..\App\Data.xls
(ooxml, embedded labels, table is Sheet1)
Where Year > '$(vMaxYear)'
Where Month > '$(vMaxMonth)';

[kaushiknsolanki · Accepted solution]

Hi,

   Try this.

   ExistingTable:
LOAD Field1
     ,Field2
     ,Year
     Month
..\App\Existing.qvd (qvd);


Temp:
Load Max(Year) AS MaxYear,Max(Month) AS MaxMonth Resident ExistingTable;



let MaxYear = peek('MaxYear',0,'Temp');

let MaxMonth = peek('MaxMonth',0,'Temp');


Concatenate(ExistingTable)


LOAD Field1
     ,Field2
     ,Year
     ,Month   
FROM
..\App\Data.xls
(ooxml, embedded labels, table is Sheet1)
Where Year > '$(vMaxYear)'
Where Month > '$(vMaxMonth)';

Regards,

Kaushik Solanki

[#5]

Hi Koushik

Thanks for your help. I think there is one small mistake in the script given by you. I think you forgot to add v infront of the variable name. If it is like that only then please let me know.

let vMaxYear = peek('MaxYear',0,'Temp');
let vMaxMonth = peek('MaxMonth',0,'Temp');

If the above changes are correct then I think I am passing the where condition('$(vMaxYear)' and '$(vMaxMonth)') is also correct only. If the above changes are incorrect then do I need to pass '$(MaxYear)' and '$(MaxMonth)' instead of ('$(vMaxYear)' and '$(vMaxMonth)').

For your information, I have created the variable vMaxYear = Max(Year) and vMaxMonth=Max(Month).

LOAD Field1 
     ,Field2
     ,Year
     ,Month    
FROM
..\App\Data.xls
(ooxml, embedded labels, table is Sheet1)
Where Year > '$(vMaxYear)'
Where Month > '$(vMaxMonth)'; 

Please tell me the exact changes are that are required in the script please.

Thanks

Attitude

[kaushiknsolanki]

Hi,

   Yes you are right,

   But its a variable name so you can write anything.

   But only thing is you need to make sure that you call that name to get the values.

    If your problem solved please mark this as answered.

Regards,

Kaushik Solanki

[#7]

Hi Kaushik

Thanks for your reply. I have edited my post just few mins back. Please read it again.

If it is a variable name then shall I remove the variable which I have created? and use the following in the where condition?

let vMaxYear = peek('MaxYear',0,'Temp');
let vMaxMonth = peek('MaxMonth',0,'Temp');

[kaushiknsolanki]

Hi,

   I have used with variable, but not directly. But you can try using directly too.

   And the where condition should be like this.

   LOAD Field1
     ,Field2
     ,Year
     ,Month   
FROM
..\App\Data.xls
(ooxml, embedded labels, table is Sheet1)
Where Year > '$(vMaxYear)' and
Month > '$(vMaxMonth)';

Regards,

Kaushik Solanki

[#9]

Hi Koushik

May I know what is the use of peek? Though we are already calculating the the Max(Year/Month) in the temp table itself. Please explain!

Still it is not working as per my expectation. Once it works defiently I will mark it as a answer.

Thanks for your support and time.

Regards

Attitude

[kaushiknsolanki]

HI,

   You have created a table which have max and min values. But you cant use it as a variable, as variable and field are different.

   So peek function is used to select the first value from the field.

   Thus we have used 

   let vMaxYear = peek('MaxYear',0,'Temp');
let vMaxMonth = peek('MaxMonth',0,'Temp');

   This will peek the first value from the MaxYear field coming from Temp table.

Regards,

Kaushik Solanki