Create a field that looks on the last 8 quarters to see if out of those 8, 6 have negative gp

[yzzy2bones]

Hi Everyone!

I am having some trouble trying to figure out a logic behind this. Basically I have lets say like this:

Load DateYYYYMM ,

           SerialNumber,

           GP

FROM qvd.

I want to have a field that look on the last 8 quarters and if out of those 8 we have 6 (not necessarily consecutive)that have had negative GP, to give me flag.

Pretty stuck on this, would really appreciate it you guys could give me some ideeas.

Thanks!

[HirisH_V7]

Sample data will explain it better.. 

Anyway form this "YYYYQQ" Field first  from your date.

Then you can switch between years and quarters as requited.

[yzzy2bones]

Thats the thing, I need a field that automatically looks on the last 8 and as said, if 6 are negative, have them flagged in that field. I can easily do something in which I can switch it myself, the whole purpose is to have that being done automatically for later complex analysis.

[HirisH_V7]

Could you please explain this with sample data .. To avoid gaps 

[yzzy2bones]

The data that I am working with its pretty big and hard to extrapolate a sample from it, but created an inline summarizing the fields necessary.

Added 2 serial numbers, with 9 quarters each. As said, cant really figure out how to make a calculated field that looks on the date column and particularity look out the latest 8 quarters and if in those 8, 6 has negative gp, have it flagged.

Sorry for the clumsy way of showing the sample data, the actual script would look as above in the 1st post.

Thanks

[Saravanan_Desingh]

As per your sample, I didn't see any 6 count of -ve GPs. But I may misunderstood your requirement. Anyway please check my solution.

tab1:
LOAD *, QuarterName(ReportYYYYMM) As Qtr INLINE [
SerialNumber,ReportYYYYMM,GP
1,1/1/2011,60
1,1/2/2011,-20
1,1/3/2011,45
1,1/4/2011,38
1,1/5/2011,-100
1,1/6/2011,-20
1,1/7/2011,55
1,1/8/2011,-20
1,1/9/2011,60
1,1/10/2011,-20
1,1/11/2011,60
1,1/12/2011,-20
1,1/1/2012,60
1,1/2/2012,-20
1,1/3/2012,45
1,1/4/2012,-300
1,1/5/2012,-100
1,1/6/2012,-20
1,1/7/2012,55
1,1/8/2012,-20
1,1/9/2012,0
1,1/10/2012,-20
1,1/11/2012,60
1,1/12/2012,-20
1,1/1/2013,-20
1,1/2/2013,60
1,1/3/2012,-20
2,1/1/2011,90
2,1/2/2011,20
2,1/3/2011,35
2,1/4/2011,28
2,1/5/2011,-100
2,1/6/2011,-40
2,1/7/2011,55
2,1/8/2011,-20
2,1/9/2011,60
2,1/10/2011,-20
2,1/11/2011,80
2,1/12/2011,-20
2,1/1/2012,60
2,1/2/2012,-90
2,1/3/2012,45
2,1/4/2012,-300
2,1/5/2012,-100
2,1/6/2012,-20
2,1/7/2012,55
2,1/8/2012,-20
2,1/9/2012,0
2,1/10/2012,-20
2,1/11/2012,60
2,1/12/2012,-20
2,1/1/2013,-20
2,1/2/2013,60
2,1/3/2012,-20
];

tab2:
LOAD SerialNumber, Qtr, Sum(GP) As QtrGP, Sign(Sum(GP)) As QtrSign
Resident tab1
Group By SerialNumber, Qtr
;
Left Join(tab2)
LOAD SerialNumber, -Sum(QtrSign=-1) As CountSign, If(-Sum(QtrSign=-1)=6,'Y','N') As FlagSign
Resident tab2
Group By SerialNumber
;

Drop Table tab1;

[Saravanan_Desingh]

[yzzy2bones]

Thanks, the only issue would be that when I load the qvd's (the inline table was just a sample, i have multiple qvds, with multiple fields that have more than 1 mil records each hence I cant use the drop tab1 and creates a syn key between sn and qtr) the app just freezes and when I load just one qvd works.   Know why this happens?