Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a simple problem, but I am struggling with it already 2 hours with poor result.
I have the following statement:
LOAD [Group acct],
[Group Acc Description],
Account,
[G/L acct long text],
BusA,
[Profit Ctr],
Tr.Prt,
Dv,
Market,
[Total HC] as Amount_Total,
External as Amount_Ext,
Internal as Amount_Int,
subfield(FileBasename(),'_',1) as Month,
subfield(FileBasename(),'_',2) as Country
FROM
[Basic_Data\*.xlsx]
(ooxml, embedded labels, table is Sheet1);
I would like to redefine Amount_Total variable in the following way:
If (Country = 'PL' , [Total HC] / 4 , [Total HC]) as Amount_Total
and in similar way the wariables Amount_Ext and Amount_Int
Could you please help me doing this?
You could either do
LOAD *,
If (Country = 'PL' , [Total HC] / 4 , [Total HC]) as Amount_Total;
LOAD [Group acct],
[Group Acc Description],
Account,
[G/L acct long text],
BusA,
[Profit Ctr],
Tr.Prt,
Dv,
Market,
[Total HC], //remove the rename here
External as Amount_Ext,
Internal as Amount_Int,
subfield(FileBasename(),'_',1) as Month,
subfield(FileBasename(),'_',2) as Country
FROM
[Basic_Data\*.xlsx]
(ooxml, embedded labels, table is Sheet1);
or
LOAD [Group acct],
[Group Acc Description],
Account,
[G/L acct long text],
BusA,
[Profit Ctr],
Tr.Prt,
Dv,
Market,
If (subfield(FileBasename(),'_',2) = 'PL' , [Total HC] / 4 , [Total HC]) as Amount_Total
External as Amount_Ext,
Internal as Amount_Int,
subfield(FileBasename(),'_',1) as Month,
subfield(FileBasename(),'_',2) as Country
FROM
[Basic_Data\*.xlsx]
(ooxml, embedded labels, table is Sheet1);
in the first example you could drop field [Total HC] if you want.
Hi
thanks.. of course, now I see my mistake. Instead of using the formula in "if" statememnt I was using the name created in the same statement...
Thanks a lot for this hint
//Draszor