Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
AI: |
LOAD |
Grca Account&'_'&"Detail Product Code"&'_'&"Ip Lcl Ref Cde Ith" as primkey, |
if(isnum("Arr Lcl Num Ath"), num("Arr Lcl Num Ath",'###################'),text("Arr Lcl Num Ath")) as ALN, |
"Ip Lcl Ref Cde Ith", |
if(len("Ip Lcl Ref Cde Ith")<=1, 'null','') as nullchk, |
"Grca Account", |
"Ip Shrt Name Ith", |
"Period Date 42" as t0_period, |
"Grca Custom3" as NACE, |
"Grca Custom6" as t0_stage, |
"Grca Primary Account", |
"Detail Product Code", |
'On' as "On/Off", |
sum("Ytd Bal")/1000 as t0_GCV_HKD000 |
FROM [lib://QS_Dev/AI80010_6_LQ-2.xlsx] |
(ooxml, embedded labels, table is data) |
WHERE ([Ytd Bal]>1 or [Ytd Bal]<-1) |
Group by "Arr Lcl Num Ath","Ip Lcl Ref Cde Ith","Ip Shrt Name Ith","Detail Product Code","Grca Account", |
Period Date 42,"Grca Primary Account","Grca Custom3","Grca Custom6"; |
//*********************************************************************************** |
outer join(AI) |
LOAD |
Grca Account&'_'&"Detail Product Code"&'_'&"Ip Lcl Ref Cde Ith" as primkey, |
'On' as "On/Off", |
if(isnum("Arr Lcl Num Ath"), num("Arr Lcl Num Ath",'###################'),text("Arr Lcl Num Ath")) as ALN, |
"Ip Lcl Ref Cde Ith", |
if(len("Ip Lcl Ref Cde Ith")<=1, 'null','') as nullchk, |
"Grca Account", |
"Ip Shrt Name Ith", |
"Period Date 42" as t1_period, |
"Grca Custom3" as NACE, |
"Grca Custom6" as t1_stage, |
"Grca Primary Account", |
"Detail Product Code", |
sum("Ytd Bal")/1000 as t1_GCV_HKD000 |
FROM [lib://QS_Dev/AI80010_6_CQ.xlsx] |
(ooxml, embedded labels, table is data) |
WHERE ([Ytd Bal]>1 or [Ytd Bal]<-1) |
Group by "Arr Lcl Num Ath","Ip Lcl Ref Cde Ith","Ip Shrt Name Ith","Detail Product Code","Grca Account", |
Period Date 42,"Grca Primary Account","Grca Custom3","Grca Custom6"; |
Hi, I don't know why it doesn't show, it should return an error on the first row it there was no rows loaded. You can add an "exit script" sentence after loading AI table to check what it has.
And yes, in the Group by you need to add all fields not used in an aggregation function.
I suppose it's a typo... Period Date 42 on the Group by should be between double quotes or brackets: "Period Date 42"