topic Re: reload on certain condition in QlikView

reload on certain condition

vireshkolagimat — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: reload on certain condition

robert_mika — Thu, 20 Oct 2016 11:26:17 GMT

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.

Re: reload on certain condition

vireshkolagimat — Thu, 20 Oct 2016 12:04:13 GMT

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.

Re: reload on certain condition

robert_mika — Thu, 20 Oct 2016 12:12:09 GMT

Could your post your excel file?

Re: reload on certain condition

vireshkolagimat — Thu, 20 Oct 2016 12:27:55 GMT

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

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

Re: reload on certain condition

Anil_Babu_Samineni — Thu, 20 Oct 2016 12:56:42 GMT

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

];

FinalTable:

Load * Resident Excel;

FinalCopy:

noconcatenate

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

In fact, This which i understand

Re: reload on certain condition

vireshkolagimat — Thu, 20 Oct 2016 13:13:12 GMT

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.

Re: reload on certain condition

mikael_bjaveclo — Thu, 20 Oct 2016 15:28:21 GMT

// 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.

Re: reload on certain condition

robert_mika — Thu, 20 Oct 2016 15:31:09 GMT

Try this:

(Replace both inline tables with your Excel path)

set v_initVerCheck=0;

verCheck:

LOAD * INLINE [

Ver_num

];

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;

Re: reload on certain condition

vireshkolagimat — Sat, 22 Oct 2016 07:55:42 GMT

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

Re: reload on certain condition

vireshkolagimat — Mon, 24 Oct 2016 11:02:20 GMT

Can we store the value as boolean. i.e it should show "True" if both the version number matches else "False".