2 Replies Latest reply: Nov 18, 2010 5:36 PM by mauich123 RSS

    Two where clauses

    mauich123

      Hi

      I'm wondering what the best/Fastest way to create a table with "two" where-clauses would be.

      This is how I have thought doing it

      RawData:
      LOAD
      Field1,
      Field2,
      Field3,
      Field4,
      Date
      from someqvdfile;

      Dates:
      load Date resident RawData;

      Let a = NoOfRows('Dates')
      FOR I=0 to a
      Let DateNow=fieldvalue('Date',I)

      Table1:
      LOAD
      Field1,
      Field2,
      '0' as Field3,
      sum(Field4),
      $(DateNow) as TheDate
      resident RawData where Date<=$(DateNow);

      ADD LOAD
      Field1,
      Field2,
      sum(Field3),
      '0' as Field4
      $(DateNow) as TheDate
      resident RawData where Date=$(DateNow);

      ... and then clear all entries where both Field3=0 and Field4=0 etc...

      See what I'm trying to do here? In Table1 I want Field4 to be summed for all dates up to $(DateNow) and this works (verified) and then to this table I want to add the sum of Field3 for ONLY the date on $(DateNow). My solution seems correct in my eyes but doesnt seem to work. What am I doing wrong? Field3 becomes 0 on all entries

      Thankful for help!

        • Two where clauses
          Karl Pover

          In the row with sum(Field3), you have to define it alias or sum(Field3) as Field3.

          ADD is for partial loads. Maybe you are doing a partial load, but you might be looking to concatenate the tables. It that's the case you should use the function CONCATENATE.

          If you have a sum in the script, the other fields in the load have to be included in a group by:

          Load Field1,
          Field2,
          sum(Field3) as Field3
          Resident Table
          Group by Field1,Field2;

          That's all I can see for now. I hope that helps.

          Regards.

          Regards.