If in load script drives me crazy...

[patricesalem]

Hello 

I get an invalid script using this load script - error coming from the line with IF function :

LOAD
    client,
    type_cde,
    commande,
    poste,
    sum(qte_livree) as QTY_shipped_auto,
   if (date_fin_OM<>'00/00/0000',max(date(date_fin_OM,'DD/MM/YYYY')),'RIEn') as toto
FROM [lib://Orders_Management (mailleux_patrices)/ORDERS_SHIPPED_AUTO.xlsx]
(ooxml, embedded labels, table is Sheet1)
where 
date(date_fin_OM,'DD/MM/YYYY') >= '01/01/2018' and
type_cde='TA'
group by type_cde,client,commande,poste;

no syntax error with :

LOAD
    client,
    type_cde,
    commande,
    poste,
    sum(qte_livree) as QTY_shipped_auto,
   if (client = '00/00/0000',max(date(date_fin_OM,'DD/MM/YYYY')),'RIEn') as toto
FROM [lib://Orders_Management (mailleux_patrices)/ORDERS_SHIPPED_AUTO.xlsx]
(ooxml, embedded labels, table is Sheet1)
where 
date(date_fin_OM,'DD/MM/YYYY') >= '01/01/2018' and
type_cde='TA'
group by type_cde,client,commande,poste;

no invalid syntax neither with :

LOAD
    client,
    type_cde,
    commande,
    poste,
    sum(qte_livree) as QTY_shipped_auto,
 
max(date(date_fin_OM,'DD/MM/YYYY')) as toto

FROM [lib://Orders_Management (mailleux_patrices)/ORDERS_SHIPPED_AUTO.xlsx]
(ooxml, embedded labels, table is Sheet1)
where 
date(date_fin_OM,'DD/MM/YYYY') >= '01/01/2018' and
type_cde='TA'
group by type_cde,client,commande,poste;

Any idea why when I test the field  “date_fin_OM” in the if statement I get the syntax error message ?

thanks

[MayilVahanan · Accepted solution]

Hi

LOAD
client,
type_cde,
commande,
poste,
sum(qte_livree) as QTY_shipped_auto,
if (date_fin_OM<>'00/00/0000',max(date(date_fin_OM,'DD/MM/YYYY')),'RIEn') as toto
FROM [lib://Orders_Management (mailleux_patrices)/ORDERS_SHIPPED_AUTO.xlsx]
(ooxml, embedded labels, table is Sheet1)
where
date(date_fin_OM,'DD/MM/YYYY') >= '01/01/2018' and
type_cde='TA'
group by type_cde,client,commande,poste;

In the above statement, date_fin_OM is used in If condition, so u need to include in Group By in order to avoid syntax error.

For ur case, try like below

Alt(date(max(if (date_fin_OM<>'00/00/0000',date_fin_OM)),'DD/MM/YYYY'),'RIEn') as toto

[patricesalem]

thanks @MayilVahanan !

I have tried without the date_fin_om (in the group by) and the expression :

Alt(date(max(if (date_fin_OM<>'00/00/0000',date_fin_OM)),'DD/MM/YYYY'),'RIEn') as toto

and it works like a charm.

I'm now trying to adapt your expression to my real case, hope it will work (I will try once the backup of the server is over :

,Alt(weekyear(date(max(if (len(date_fin_OM) > 0 and date_fin_OM<>'00/00/0000',date_fin_OM)),'DD/MM/YYYY'))
& num(week(date(max(if (len(date_fin_OM) > 0 and date_fin_OM<>'00/00/0000',date_fin_OM)),'DD/MM/YYYY'))),
weekyear(max(date(date_las,'DD/MM/YYYY'))) & num(week(max(date(date_las,'DD/MM/YYYY'))),'00')) as WEEK_OM_shipped_auto

I will let you know if it works !
Thanks again
Pat