2 Replies Latest reply: Dec 19, 2011 2:12 AM by Robert Szarek RSS

    Adding formula created after Load statement to this statement

    Robert Szarek

      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?

        • Re: Adding formula created after Load statement to this statement
          Stefan Wühl

          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.