3 Replies Latest reply: Feb 1, 2017 5:28 AM by Benazir Jamin RSS

    Checking values across months

    Wenjun Wu

      Hi,

       

      Scenario 1:

      Can anyone suggest a best dynamic method to check the values across existing data month columns. (data may not have full 12 months)

      My data format is as below, one month's value is recorded in one column.

      I need to flap those customer having  consistent value of 10 throughout the year.

      CustomerJan2016Feb2016Mar2016Apr2016May2016Jun2016Jul2016Aug2016Sept2016Oct2016Nov2016Dec2016
      A101010101010101010101010
      B7834323223312123417345689
      C101010101010101010101010

       

      Resulting new column : Flap

      CustomerFlap
      A1
      B0
      C1

       

      Scenario 2:

      If my data structure changed to :

      CustomerValuesMonthYear
      A10

      Jan2016

      A10Feb2016
      A10Mar2016
      A10Apr2016
      A10May2016
      A10Jun2016
      A10Jul2016
      A10Aug2016
      A10Sept2016
      A10Oct2016
      A10Nov2016
      A10Dec2016
      B78Jan2016
      B343Feb2016
      B23Mar2016
      B22Apr2016
      B33May2016
      B12Jun2016

       

      How to check that the value is 10 consistently throughout current number of months (may not have 12 months data) ?

      A new column Flap is also generated.

       

      Thank you for your help!

        • Re: Checking values across months
          Marcus Sommer

          I would transform the source per The Crosstable Load into a normal data-structure like your scenario two and you could add per left join an aggregation-load with count(YearMonth) on Customer and a where-clause on the Values which aren't NULL of these data to get the number of months with Values for each Customer - and you might take this result to compare it against your expected result to flag them.

           

          If you really want to check that each monthly Value is higher as certain threshold than you will need to use interrecord-functions within a sorted resident-load to check the values of the previous loaded records - for this see here: Peek() or Previous() ?

           

          - Marcus

            • Re: Checking values across months
              Wenjun Wu

              Hi Marcus,

               

              I have tried to use Crosstable to turn into scenario 2, I create a dimension Flap, but my expression returns 1 whenever the customer account has a value 10 in any of the months.

              if(Values = 10.0, 1, 0)  how to add on the part to check for every months?

               

              From your suggestion above, can I understand that I should count(MonthYear), say = 12.

              so I will

              aggr( count(if(Values = 10.0, Values)), Customer)

              If the count result is also 12, then it will be flapped.

               

              Thank you!

            • Re: Checking values across months
              Benazir Jamin

              Suppose the data is in the "data_table" in the form of Scenario 2.

              The below code first creates a new column "value_check" which has 1 in a row if all the "Values" are 10 for a Customer till that row. Then for every Customer we check the last Value. I am assuming the data is ordered by Customer, MonthYear.

              This way you get a new table with Customers and the "flap" value.

               

              data_table_2:
              load
                *,
                  if(peek(Customer)=Customer and peek(Values)=Values 
                  and Values=10 and peek(value_check)<>0,1,
                      if(peek(Customer)<>Customer and Values=10,1,0)) as value_check
              resident data_table;
              
              Flap_table:
              Load 
                Customer,
                  LastValue(value_check) as flap
              resident data_table_2
              group by Customer;