0 Replies Latest reply: Feb 7, 2018 9:26 AM by Piotr Amarowicz RSS

    Exclude data in load editor or using Set Analysis based on complex condition

    Piotr Amarowicz

      Hi,

       

      Suppose we have a table like the one below. I would like to exclude from the whole model dimension which in period = 0 has value < 10. Is there an elegant way to achive this in Load Editor in one Load or in formulas using Set Analysis (without changing anything in Load Editor)?

       

      test:

      Load * Inline [

      dimension,period,value

      a,0,8

      b,0,12

      c,0,13

      a,1,15

      b,1,20

      c,1,25

      ];

       

       

      I managed to do this but I'm wondering if there is a better, clever way do do this. My answer:

       

      test:

      Load * Inline [

      dimension,period,value

      a,0,8

      b,0,12

      c,0,13

      a,1,15

      b,1,20

      c,1,25

      ];

       

      // add flag which dimension should be excluded

      Left Join(test)

      Load distinct

      dimension,

          'true' as dimension_exclude

      Resident test

      Where period = 0 AND value < 10;

       

      test_stage2:

      Load

      dimension,

          period,

          value

      Resident test where dimension_exclude <> 'true';

      drop table test;