1 Reply Latest reply: Nov 25, 2011 1:02 PM by Stefan Wühl RSS

    Error: Invalid Expression

      Hi Dear QlikCoummunity Friends,

       

      I have this script, but i don't know what is the error

      --------------------------------------------------------------------------------------------------------------------------

      LOAD If(UPPER(DATAAREAID)= 'CUC' and DIMENSION = '11001','HOL',UPPER(DATAAREAID))&'-'& DIMENSION &'-'&

           Year(Date(TRANSDATE,'DD/MM/YYYY')) & Mid(Text(Date(TRANSDATE,'DD/MM/YYYY')),4,2) AS Llave_PyG,

           Year(Date(TRANSDATE,'DD/MM/YYYY')) & Mid(Text(Date(TRANSDATE,'DD/MM/YYYY')),4,2) as Llave_TRM_PyG,

           Sum(AMOUNTMST) as Valor_PyG,

           ApplyMap('MapeoMoneda', 'USD_' & Year(Date(TRANSDATE,'DD/MM/YYYY')) & Mid(Text(Date(TRANSDATE,'DD/MM/YYYY')),4,2)) as USD_PyG,

           ApplyMap('MapeoMoneda', 'COP_' & Year(Date(TRANSDATE,'DD/MM/YYYY')) & Mid(Text(Date(TRANSDATE,'DD/MM/YYYY')),4,2)) as COP_PyG

          ,'Gastos por intereses' as Indicador_PyG_ES

          ,'Interest expense' as Indicador_PyG_EN

          ,8 as Orden

      FROM

      Movimientos.qvd(qvd)

      WHERE MID(ACCOUNTNUM,1,4) = '5801'

      GROUP BY If(UPPER(DATAAREAID)= 'CUC' and DIMENSION = '11001','HOL',UPPER(DATAAREAID))&'-'& DIMENSION &'-'&

           Year(Date(TRANSDATE,'DD/MM/YYYY')) & Mid(Text(Date(TRANSDATE,'DD/MM/YYYY')),4,2),

           Year(Date(TRANSDATE,'DD/MM/YYYY')) & Mid(Text(Date(TRANSDATE,'DD/MM/YYYY')),4,2);

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      Somebody could help me?

        • Error: Invalid Expression
          Stefan Wühl

          Well, I think the error is caused by your group by clause:

           

          First, I think you could only state a list of fields here, you can't use an expression which also returns a concatenated value (if the if-statement is valid at all, it looks like there is one argument too much?).

           

          Do you want your group by be dependent on record values or why do you want to use an if()-statement here?

           

          Second, if you use group by, you should only use the fields that you are listed in your group by in your load, or fields within an aggregation function. I think this condition is not fulfilled, e.g. with the fields using applymap(), 'Gastos por intereses' and 'Interest expense'.

           

          If you could tell us what you want to achieve, maybe someone can help you with creating a load script.

           

          Regards,

          Stefan