topic Re: Issue with incremental load using variable in QlikView

Issue with incremental load using variable

Fri, 08 Jul 2011 06:03:36 GMT

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

Re: Issue with incremental load using variable

Fri, 08 Jul 2011 06:21:19 GMT

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

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

Thanks

Attitude

Re: Issue with incremental load using variable

Fri, 08 Jul 2011 06:31:06 GMT

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

Re: Issue with incremental load using variable

kaushiknsolanki — Fri, 08 Jul 2011 06:41:44 GMT

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

Re: Issue with incremental load using variable

Fri, 08 Jul 2011 09:41:27 GMT

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

Re: Issue with incremental load using variable

kaushiknsolanki — Fri, 08 Jul 2011 09:46:30 GMT

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

Re: Issue with incremental load using variable

Fri, 08 Jul 2011 09:58:01 GMT

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

Re: Issue with incremental load using variable

kaushiknsolanki — Fri, 08 Jul 2011 10:02:56 GMT

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

Re: Issue with incremental load using variable

Fri, 08 Jul 2011 10:07:21 GMT

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

Re: Issue with incremental load using variable

kaushiknsolanki — Fri, 08 Jul 2011 10:12:19 GMT

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

Re: Issue with incremental load using variable

Fri, 08 Jul 2011 11:08:53 GMT

Hi Koushik

Still I am little confused! Because any way "Temp" table will return only one row then what is the use Peek function to return the particular value from the first row? Why can't we use something like below?

vMaxYear = MaxYear
vMaxMonth = MaxMonth
vMaxYearMonth = MaxYearMonth

Please explain it for my better understanding!

Thanks

Attitude

Re: Issue with incremental load using variable

kaushiknsolanki — Fri, 08 Jul 2011 11:14:20 GMT

Hi,

Actually you need to tell qlikview that which value you want and from which field of which table.

You tried doing what you said, and you didnt succeed, try using peek. It will solve your problem.

Regards,

Kaushik Solanki