5 Replies Latest reply: Sep 23, 2009 3:35 PM by Peter Hübschen RSS

    Problem with adding column

    Peter Hübschen

      Hello,

       

      I am loading data from database-tables (all positions of an order) and I have to add some columns (currencys, calculations,...) . Since I want to avoid the use of one formula x times, I made it by multiple LOAD statements (see below). But with the last "load" Qlikview runs forever. If I make the whole formula for "ordersValue" in the "load"-command before and multiply it with isZFlag then it works.

       

      So what can I do to get my data without lots of formula-copies? Do you need further informations?
      I'm running QV9 Personal Edition, because I have to evaluate this software.

       

      Kind regards
      Peter

       

       

       


      ODBC
      CONNECT TO Cognos (XUserId is xxx, XPassword is xxx);

      //Position-Data
      relFB_Base:
      SQL SELECT
      <Some fields>,
      Segm2_Meng,
      Netto2,
      CASE WHEN (a + b + c) = 0
      THEN Segm2_Meng
      ELSE (a + b + c)
      END as orderquantity,
      CASE WHEN (SAint = 215 and Mnr not in ('350', '355'))
      THEN 0
      ELSE 1
      END as isZFlag,
      CASE WHEN Segm2_Term is null
      THEN Segm1_Term
      ELSE Segm2_Term
      END as Eventtime_orig,
      WE,
      CASE WHEN WE = 'Eur' THEN 1 ELSE 0 END as isEuroFlag
      FROM <table1>
      WHERE <Some conditions>

      //join data from the same table, but it is "Header"-Data, the join works correctly
      LEFT JOIN SELECT
      <Some fields>
      FROM <table1>
      WHERE <Some conditions>

      //join data from different table, it's been Currency-Data (WE is the join-Field)
      LEFT JOIN SELECT Kurs,
      Basis,
      WE
      FROM <CurrencyTable>

      //Add some columns to the base_Table
      relFB_Temp1:
      LOAD *,
      if (Segm2_Meng <> 0,
      if(isEuroFlag = 1,
      Netto2/Segm2_Meng*orderquantity,
      ((Netto2*Kurs/Basis)/Segm2_Meng)* orderquantity), NULL())
      as ordersValue
      RESIDENT relFB_Base;

      //Add 1 column with calculation
      LOAD *, ordersValue * isZFlag as myTestField
      RESIDENT relFB_Temp1;

      //delete first table
      DROP TABLE relFB_Base;




        • SV:Problem with adding column

          I think your problem is that you get a large synthetic key with your "Add 1 column with calculation" load.

          You could do it with a presceding load:

           

           

          //Add some columns to the base_Table
          relFB_Temp1:

          LOAD *,
          ordersValue*isZFlag as myTestField;
          LOAD *,
          if (Segm2_Meng <> 0,
          if(isEuroFlag = 1,
          Netto2/Segm2_Meng*orderquantity,
          ((Netto2*Kurs/Basis)/Segm2_Meng)* orderquantity), NULL())
          as ordersValue
          RESIDENT relFB_Base;

          //delete first table
          DROP TABLE relFB_Base;

          /Michael