Comment out the part of script via sub routines

[beck_bakytbek]

Hi Folks,

i have the following situation and i am using in my script- area: the incremental load. It does look like:

// main:

// LOAD

//     mit_id,

//     mit_name,

//     mit_gehalt,

//     mit_datum

// FROM [lib://Excel/Mitarbeiter.xlsx]

// (ooxml, embedded labels, table is Tabelle1);

// Store main into [lib://QVD/main.qvd];

temp_main:

load

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd);

Max_Value:

load Max(mit_datum) as MaxWert

Resident temp_main;

let vNeu = peek('MaxWert',-1,'Max_Value');

drop Table temp_main;

main_neu:

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://Excel/Mitarbeiter.xlsx]

(ooxml, embedded labels, table is Tabelle1) where mit_datum >= $(vNeu);

Concatenate

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd)where not Exists(mit_id);

Store main_neu into [lib://QVD/main.qvd];

As you see, i commented out the first part of script. My questions is:

Is there any possibilities or technique to comment out the first part of script via sub routines?

Thanks a lot

Beck

[petter]

Yes you can "comment" out with sub routines. Another alternative is to use the multi-line comment starting with a /* and ending later in the script with a */.

In your case it could look like:

/*

main:

LOAD

     mit_id,

     mit_name,

     mit_gehalt,

     mit_datum

FROM [lib://Excel/Mitarbeiter.xlsx]

(ooxml, embedded labels, table is Tabelle1);

Store main into [lib://QVD/main.qvd];

*/

temp_main:

load

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd);

or with a subrutine:

Sub LoadMain

   main:

   LOAD

     mit_id,

     mit_name,

     mit_gehalt,

     mit_datum

  FROM [lib://Excel/Mitarbeiter.xlsx]

  (ooxml, embedded labels, table is Tabelle1);

  Store main into [lib://QVD/main.qvd];

End Sub

// CALL LoadMain; 

temp_main:

load

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd);

[beck_bakytbek]

Hi Petter,

thanks a lot for your help and time, the first option with /* i know that,

but how can i comment out first part of script when i reloed the entire script, shoud i create any conditions or if..end, do you have any idea? or how can check that i store the date in my folder and my folder is not empty?

thanks a lot

Beck

[OmarBenSalem]

U want to execute only the first part once? (if the qvd is not created?) and then comment it?

and use the other script?

What's the condition once achieved u want to comment ur script beck?

[beck_bakytbek]

Hi Omar,

thanks a lot for your responce,

i want exclude the first part of script when i cretae a qvd

[OmarBenSalem]

so basically, if the qvd do not exist, execute the first script;

if the qvd exists , execute the second part; is that so?

try sthing like :

- handle error mode (qlik won't stop if it finds errors) : SET ErrorMode=0;

- Try to import the main table from the qvd :

* if it does not exist (ScriptError >0):

wait 2 seconds then create ur qvd (the first script)

else (if it does exist (the qvd)):

=> drop ur main table u just created (to test the existing of the qvd)

and run ur second script:

SET ErrorMode=0;

main:

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd];

if ScriptError >0

then

sleep 2000;

main:

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://Excel/Mitarbeiter.xlsx]

(ooxml, embedded labels, table is Tabelle1);

Store main into [lib://QVD/main.qvd];

else

drop table main;

temp_main:

load

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd);

Max_Value:

load Max(mit_datum) as MaxWert

Resident temp_main;

let vNeu = peek('MaxWert',-1,'Max_Value');

drop Table temp_main;

main_neu:

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://Excel/Mitarbeiter.xlsx]

(ooxml, embedded labels, table is Tabelle1) where mit_datum >= $(vNeu);

Concatenate

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd)where not Exists(mit_id);

Store main_neu into [lib://QVD/main.qvd];

EndIf

set ErrorMode=1;

[beck_bakytbek]

Hi Omar

thanks a lot for your help, i tried but i recieve this message (see screenshot)

[beck_bakytbek]

Hi Omar, this is my testdata

[marcus_sommer]

In most of my loadings I use a variable (loaded from an external excel) which is queried within an if-loop to determine how the load should be executed. Simplified it looked:

if '$(vLoadArt)' = 'full' then

     load * from Source;

elseif '$(vLoadArt)' = 'partial' then

     load * from qvd where Period <= $(vPeriod);

          concatenate

     load * from Source where Period > $(vPeriod);

elseif '$(vLoadArt)' = 'incremental' then

     load * from qvd;

          concatenate

     load * from Source where not exists(Key);

end if

But just to comment out the initial load or some further load-parts is not a bad idea and quite practically - the normal case will be the incremental loading and if something unusual happens which requires a full-load and/or some adjustements to the load-statement you could just apply these things manually. An automatic way of handling any exceptions will be quite expensive and often even not possible.

- Marcus

[OmarBenSalem · Accepted solution]

try with this::

LET ErrorMode=0; // change set to let

main:

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd);

if scriptError >0

then

sleep 2000;

//u were calling this from the qvd, while there is no qvd...

main:

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://Excel/Mitarbeiter.xlsx]

(ooxml, embedded labels, table is Tabelle1);

Store main into [lib://QVD/main.qvd];

else

drop table main;

temp_main:

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd);

Max_Value:

load Max(mit_datum) as MaxWert

Resident temp_main;

let vNeu = peek('MaxWert',-1,'Max_Value');

drop Table temp_main;

main_neu:

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://Excel/Mitarbeiter.xlsx]

(ooxml, embedded labels, table is Tabelle1)

where mit_datum >= $(vNeu);

Concatenate

LOAD

    mit_id,

    mit_name,

    mit_gehalt,

    mit_datum

FROM [lib://QVD/main.qvd]

(qvd)where not Exists(mit_id);

Store main_neu into [lib://QVD/main.qvd];

EndIf

let ErrorMode=1;

if the qvd exists and I run this:

result

Now, if I delete the main.qvd file and run :

and the qvd will be created again: