Checking values across months

[wenjun2016]

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.

Customer	Jan2016	Feb2016	Mar2016	Apr2016	May2016	Jun2016	Jul2016	Aug2016	Sept2016	Oct2016	Nov2016	Dec2016
A	10	10	10	10	10	10	10	10	10	10	10	10
B	78	343	23	22	33	12	12	34	17	34	56	89
C	10	10	10	10	10	10	10	10	10	10	10	10

Resulting new column : Flap

Customer	Flap
A	1
B	0
C	1

Scenario 2:

If my data structure changed to :

Customer	Values	MonthYear
A	10	Jan2016
A	10	Feb2016
A	10	Mar2016
A	10	Apr2016
A	10	May2016
A	10	Jun2016
A	10	Jul2016
A	10	Aug2016
A	10	Sept2016
A	10	Oct2016
A	10	Nov2016
A	10	Dec2016
B	78	Jan2016
B	343	Feb2016
B	23	Mar2016
B	22	Apr2016
B	33	May2016
B	12	Jun2016

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!

[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

[wenjun2016]

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!

[polestar_suppor]

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;