Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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