reload on certain condition

[vireshkolagimat]

Hi Everyone,

I have an Excel file and which contain a column "Ver_num" in a sheet called ver_mgmt. After every changes to the source file, the Ver_num will increase by one. I want to load file if the version number matches with the current version number else it should give me a pop up saying the version number is updated.

suppose the current version number is 5 and i have loaded the file. now my QVW will have the version number 5. After making changes to the file , i will update the version number in excel file. Now the script should check for the latest version number and load the data into qvw.

I thought of creating a variable to store the max of the version number. But how i can look for the latest version number?

Regards,

Viresh

[robert_mika]

Look at incremental load

Your variable will be the max number in your Ver_num column

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

and combine with if/else statement with Exit Script.

[vireshkolagimat]

basically what i am looking is if the source file has ver_num say 8 and qvw file has ver_num 7 then the reload should happen if it is other way, the reload should not happen.

[robert_mika]

Could your post your excel file?

[vireshkolagimat]

Hi , Due to some concerns, i am not able to upload the file. This is how my excel file looks(first column with column header Ver_num):

Ver_num

1

2

3

Once i load the above data, my qvw will have the same data i.e ver_num 1,2 and 3. If i update the version number 4 in excel, i should reload the doc now because ver_num 4 is not available in qvw.

So i don't want reload to happen if the ver_num in excel is less than the ver_num in QVW file.If version number in excel is equal to or greater than the version number in qvw file, the reload should happen.

let me know if you have any questions.

Thanks

[Anil_Babu_Samineni]

Shall i ask Two questions

1) Does this 4 value will updating from Existing Value?

2) You don't want to reload, Then what you are required(Still Confusing)

Excel:

Load * Inline [

Ver_num

1

2

3

];

FinalTable:

Load * Resident Excel;

FinalCopy:

noconcatenate

Load * Resident FinalTable where Ver_num >= Max(Ver_num);

In fact, This which i understand

[vireshkolagimat]

I am comparing the version number in the excel file and qvw file. The version number will updated in the excel file. The reload should happen if the latest version number is not available in the qvw file, else the reload should fail.

[mikael_bjaveclo]

// Check for updates
VerNum:
LOAD Max(Ver_num) As MaxVerNum
FROM
[MyVerNums.xlsx]
(ooxml, embedded labels, table is Sheet1);

Let tTestedVerNum = FieldValue('MaxVerNum', 1); // Save the fieldvalue into a temp variable
Drop Table VerNum;

If Not vLatestVerNum Then
// We have never checked this variable before
  Let vLatestVerNum = 0; // This is the stored variable
End If

If tTestedVerNum > vLatestVerNum Then
// A higher version. Reload
  Trace A new set of data. Reloading...;

T1:
Load Fields
From Sample.qvd (qvd);

// Don't forget to save this new VerNum
  Let vLatestVerNum = tTestedVerNum;
Else
Trace We have already loaded this data once...;
End If

Set tTestedVerNum=; // Undeclare
// Don't forget to save the document.

[robert_mika]

Try this:

(Replace both  inline tables with your Excel path)

set v_initVerCheck=0;

verCheck:

LOAD * INLINE [

Ver_num

1

2

3

];

Last_Update:

LOAD

MAX(Ver_num) as MaxNewNumber

Resident verCheck;

LET v_maxNewNum=peek('MaxNewNumber',0,'Last_Update');

if $(v_maxNewNum) > $(v_initVerCheck) then

Excel:

LOAD * INLINE [

Ver_num,Letter

1,A

2,B

3,C

];

set v_initVerCheck = $(v_maxNewNum);

ELSEIF  $(v_maxNewNum)<= $(v_initVerCheck) then

exit Script

end if;

drop table verCheck;

[vireshkolagimat]

Hi, Thanks for your help.

I am attaching a sample file and qvw for better understanding.

Each time if there are any changes in Excel file, the version number get changed. Whenever i reload the qvw, it should check the existing version number in QVW file and compare with the version number in excel file. Based on this i want to show the message in text as shown in sample qvw file. Each time the reload should happen irrespective of whether the version number in excel and qvw match or not.

Please let know if i am not clear.I really appreciate your help.

Regards,

Viresh