Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have just solved my latest difficulty and triggered the loading of a constructed record to a historization_qvd in case the LOAD from the database brought no records.
=> That brings me up to my next question:
Instead of going, like
LOAD
IF(v_test='X', DATE(TODAY())) as Y,
IF(v_test='X' count(y)) as Z
RESIDENT ...
(stating the condition in every line of the LOAD statement)
can't I simply nest the entire LOAD inside the IF-condition? Or do as I did in another program, make the statement a multi-line comment in case the IF-condition returns TRUE? That way, I would save quite some typework.
Thanks a lot!
Best regards,
DataNibbler
I use in several applications what you want to achive here is a sample:
for vLoad = year(today())-5 to year(Today())+1 //dichiaro il ciclo e la variabile con il primo anno d'analizzare in questo modo non devo u e il l'anno attuale.
if isnull(filetime('$(rootFolder_f)Biological_Prod_$(vLoad).qvd')) THEN //CONTROLLO SE ESISTE UNA ULTIMA DATA DI MODIFICA E QUINDI SE ESISTE IL FILE
DATI_MARKETING: //Se il file non esiste carico dai dati concatenati generati nel manual data nella pagina precedente
LOAD YEAR,
PRODUCT,
CUSTOMER,
MONTH,
PRICE,
FOC_DISC,
BDG_QTA,
BDG_VAL,
FCST_QTA,
FCST_VAL,
IF( PRICE>0,( (100-(FOC_DISC)) * PRICE/100)* FCST_QTA,FCST_VAL) AS FORECAST,
BDG_RV_QTA,
BDG_RV_VAL,
CUSTOMER_ID,
TRX_DATE,
INVOICED_QTA,
INVOICED_CHF,
INVOICED_VAL,
BACKLOG_QTA,
BACKLOG_VAL,
RLG_QTA,
RLG_VAL,
IF( PRICE >0, PRICE*(100-( FOC_DISC))/100 * RLG_QTA, RLG_VAL) as ROLLING
FROM
'$(rootFolder)Concatenated_Data.qvd'
(qvd)
where YEAR = $(vLoad);
else //Se esiste carico direttamente dal file qvd generato dal freezing degli anni
LOAD YEAR,
PRODUCT,
CUSTOMER,
MONTH,
PRICE,
FOC_DISC,
BDG_QTA,
BDG_VAL,
FCST_QTA,
FCST_VAL,
IF( PRICE>0,( (100-(FOC_DISC)) * PRICE/100)* FCST_QTA,FCST_VAL) AS FORECAST,
BDG_RV_QTA,
BDG_RV_VAL,
CUSTOMER_ID,
TRX_DATE,
INVOICED_QTA,
INVOICED_CHF,
INVOICED_VAL,
BACKLOG_QTA,
BACKLOG_VAL,
RLG_QTA,
RLG_VAL,
IF( PRICE >0, PRICE*(100-( FOC_DISC))/100 * RLG_QTA, RLG_VAL) as ROLLING
FROM
'$(rootFolder_f)Biological_Prod_$(vLoad).qvd'
(qvd);
end if //esco dal ciclo if
next vLoad; //Incremento la variabile vLoad fino all'anno odierno dopodiche procede nella seguente pagina
In this specific case i check the existance of a file with freezed data and load it for that year.
I use in several applications what you want to achive here is a sample:
for vLoad = year(today())-5 to year(Today())+1 //dichiaro il ciclo e la variabile con il primo anno d'analizzare in questo modo non devo u e il l'anno attuale.
if isnull(filetime('$(rootFolder_f)Biological_Prod_$(vLoad).qvd')) THEN //CONTROLLO SE ESISTE UNA ULTIMA DATA DI MODIFICA E QUINDI SE ESISTE IL FILE
DATI_MARKETING: //Se il file non esiste carico dai dati concatenati generati nel manual data nella pagina precedente
LOAD YEAR,
PRODUCT,
CUSTOMER,
MONTH,
PRICE,
FOC_DISC,
BDG_QTA,
BDG_VAL,
FCST_QTA,
FCST_VAL,
IF( PRICE>0,( (100-(FOC_DISC)) * PRICE/100)* FCST_QTA,FCST_VAL) AS FORECAST,
BDG_RV_QTA,
BDG_RV_VAL,
CUSTOMER_ID,
TRX_DATE,
INVOICED_QTA,
INVOICED_CHF,
INVOICED_VAL,
BACKLOG_QTA,
BACKLOG_VAL,
RLG_QTA,
RLG_VAL,
IF( PRICE >0, PRICE*(100-( FOC_DISC))/100 * RLG_QTA, RLG_VAL) as ROLLING
FROM
'$(rootFolder)Concatenated_Data.qvd'
(qvd)
where YEAR = $(vLoad);
else //Se esiste carico direttamente dal file qvd generato dal freezing degli anni
LOAD YEAR,
PRODUCT,
CUSTOMER,
MONTH,
PRICE,
FOC_DISC,
BDG_QTA,
BDG_VAL,
FCST_QTA,
FCST_VAL,
IF( PRICE>0,( (100-(FOC_DISC)) * PRICE/100)* FCST_QTA,FCST_VAL) AS FORECAST,
BDG_RV_QTA,
BDG_RV_VAL,
CUSTOMER_ID,
TRX_DATE,
INVOICED_QTA,
INVOICED_CHF,
INVOICED_VAL,
BACKLOG_QTA,
BACKLOG_VAL,
RLG_QTA,
RLG_VAL,
IF( PRICE >0, PRICE*(100-( FOC_DISC))/100 * RLG_QTA, RLG_VAL) as ROLLING
FROM
'$(rootFolder_f)Biological_Prod_$(vLoad).qvd'
(qvd);
end if //esco dal ciclo if
next vLoad; //Incremento la variabile vLoad fino all'anno odierno dopodiche procede nella seguente pagina
In this specific case i check the existance of a file with freezed data and load it for that year.
Hi pedro,
I will look that up. So the basic construction is
IF
THEN
ELSE
End If
Thanks a lot!
Best regards,
DataNibbler
Hi,
I think I've made it - but OI will only know tomorrow whether it really works.
My construction now is as follows:
// First, I load the "historization_qvd" that is already there
LOAD
Datum_Sp1,
Items_Sp1
FROM
[..\01_QVD\Historische_qvd\Historie_Sperr1_74.qvd]
(qvd);
// I use this to determine whether any records were loaded from the qvd
Let v_Nr_Rows_Sperr1 = NoOfRows('Sperr1_AOS');
// Here comes my IF-construction. I hope this works
IF ($(v_nr_Rows_Sperr1)<>0 AND NOT EXISTS(Datum_Sp_AOS, $(v_today))) THEN
CONCATENATE (Historie_Sperr1_74)
LOAD
$(v_today) as Datum_Sp1,
count(DISTINCT BIN_ITEM_DETAIL.ITEM_NUMBER) as Items_Sp1
RESIDENT Sperr1_AOS;
ELSEIF ($(v_nr_Rows_Sperr1)=0 AND NOT EXISTS(Datum_Sp_AOS, $(v_today))) THEN
CONCATENATE (Historie_Sperr1_74)
FIRST 1
LOAD
num(TODAY()) as Datum_Sp1,
0 as Items_Sp1
RESIDENT Kalender_#1;
ENDIF
Hi,
there seems to be something very wrong:
I have now adapted the capitalization of the variable_name.
Now it runs without errors - and afterwards I get the message "Error; Recover old data?"
That is not good...
Can it be that this IF-construction cannot deal with a set of two conditions? I just tried removing one and that seems to work...
To be fair i never had a requirment that needed 2 set of conditions so i cant anserw you properly.
I will give a try to a more complex if tomorrow and let you know if is viable or not.
Hey there i did a simple script and looks like it works without problems. Check the attached qvw.
Cheers
Hi juan,
thanks a lot!
I've done it the other way for now, but I'll look at your qvw for next time.
Best regards,
DataNibbler
Hi,
Please be careful with the use of $() !
In my opinion you do not need it.(even it is not hurting either in your examples)
What I am trying to say is that the following script should work pretty same:
let variable1=100;
let variable2=200;
if variable1=100 and variable2=200 then
load .... whatever;
else
load ... somethingelse ;
end if