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() ?
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.
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;