 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| ROW Level Comaprision | Login/Join |
| Member |
I am trying to compare values in each row of a column and based on these comparisons, toggle a flag. Using LAST funtion, I could compare a row with the previous row. But I am trying to compare a row with the following row. By using COMPUTE, this is how I came up with col4 COMPUTE col4/D1 = IF col3 EQ 'Stg2' AND LAST col2 EQ 'Stg1' THEN 1 ELSE 0 col1 col2 col3 col4 col5 1 Stg1 0 0 2 Stg1 0 1 3 Stg2 1 0 4 Stg2 0 0 5 Stg2 0 0 6 Stg2 0 0 But I want the toggle to go high for the preceding row as shown in col5.Any ideas are greatly appreciated. | ||
|
| Virtuoso |
Sorry, there is no NEXT operator (prophesy is limited to the select few). But you can sort them the opposite way and HOLD, then use LAST in Define against the Hold file, and sort correctly for the report. - Jack Gross WF through 8.1.05 | |||
|
| Expert |
fex, here's a way to think about it: make your flag a cumulative flag, and then you'll be wanting to select the max value per stg. COMPUTE COL3/I5=IF COL2 NE LAST COL2 THEN 1 ELSE LAST COL3 + 1 ; gives you col1 col2 col3 1 Stg1 1 2 Stg1 2 3 Stg2 1 4 Stg2 2 5 Stg2 3 6 Stg2 4 now, you'll be wanting to flag the max of col3 for each Stg value.
| |||||
|
| Member |
I guess my table doesn't make sense. Here is a better version... col1 col2 col3 col4 col5 1 Stg1 0 0 0 2 Stg1 0 0 1 3 0 Stg2 1 0 4 0 Stg2 0 0 5 0 Stg2 0 0 6 0 Stg2 0 0 | |||
|
| Virtuoso |
Here is some code that may get you what you are looking for. TABLE FILE CAR SUM CAR AS 'NXTCAR' COMPUTE LSTCAR/A16=LAST CAR; BY COUNTRY NOPRINT BY CAR NOPRINT ON TABLE SET HOLDLIST PRINTONLY ON TABLE HOLD AS NXTCAR FORMAT FOCUS INDEX LSTCAR END -RUN JOIN CAR IN CAR TO LSTCAR IN NXTCAR -RUN TABLE FILE CAR SUM SALES NXTCAR BY COUNTRY BY CAR END Thanks! Mickey
| |||||
|
| Member |
Thank you all.. I will try these and let you guys know. Thanks again | |||
|
| Platinum Member |
Here's another example that may help. TOG1 flags the first car in the country TOG2 flags the last car in the country DEFINE FILE CAR CNT/I5 = 1; END -* TABLE FILE CAR SUM CNT BY COUNTRY COMPUTE CNTR/I5 = IF COUNTRY EQ LAST COUNTRY THEN CNTR + 1 ELSE 1; BY COUNTRY BY CAR ON TABLE HOLD END -* DEFINE FILE HOLD TOG1/I1 = IF CNTR EQ 1 THEN 1 ELSE 0; TOG2/I1 = IF CNTR EQ CNT THEN 1 ELSE 0; END TABLE FILE HOLD COUNTRY CNT CAR TOG1 TOG2 END Jim WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2 | |||
|
| Platinum Member |
Same as above without the hold file. DEFINE FILE CAR CNT/I5 = 1; END -* TABLE FILE CAR SUM CNT BY COUNTRY COMPUTE CNTR/I5 = IF COUNTRY EQ LAST COUNTRY THEN CNTR + 1 ELSE 1; COMPUTE TOG1/I1 = IF CNTR EQ 1 THEN 1 ELSE 0; COMPUTE TOG2/I1 = IF CNTR EQ CNT THEN 1 ELSE 0; BY COUNTRY BY CAR END Jim WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2 | |||
|
| Platinum Member |
This is piggy solution and hopefully maybe others can supply a more elegant solution. This is assuming that the file is already in the proper sort order for the last command. In a define build a row counter: cntr/i9=last cntr + 1; Move your compute for col4 to a define. Then table your file with cols 1-4 and sort by highest cntr and then hold it. The file should now be in inverse order. Now do a define on the hold file for col5 using the last commmand as done for col4. Then table the file and sort by cntr which would bring the file back to its original order. FOCUS 7.6 MVS PDF,HTML,EXCEL | |||
|
| Expert |
..... or simply use FML, where you can reference virtually any cell you want, preceding or following the row in which your current cell exists. Less data handling, "easier" to maintain ...... Just a thought ....... T
| |||||||||
|
| Member |
Hello All Sorry for not responding sooner! Thank you all for your responses. Since this was only a part of a much complex code and the data I am pulling into the report is huge, we have decided to put this requirement on hold. But I would still like to explore the possibilities of doing it in a simpler way and surely keep you all posted. I did work on implementing counters but that didnt help me. Tony A - I am not well versed with FML. Could you provide some mock code that I can look at. Thanks again | |||
|
| Expert |
Fex08, As per your example, supposing that your file had three columns within it, called Col1, Col2 & Col3. FML that would create the required output for you would be - TABLE FILE fmlsheet
PRINT Col2
Col3
COMPUTE Col4/D1 = 0;
COMPUTE Col5/D1 = 0;
FOR Col1
1 LABEL C1 OVER
2 LABEL C2 OVER
3 LABEL C3 OVER
4 LABEL C4 OVER
5 LABEL C5 OVER
6 LABEL C6 OVER
RECAP C2(3) = IF C2(2) EQ 'Stg2' AND C1(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C3(3) = IF C3(2) EQ 'Stg2' AND C2(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C4(3) = IF C4(2) EQ 'Stg2' AND C3(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C5(3) = IF C5(2) EQ 'Stg2' AND C4(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C6(3) = IF C6(2) EQ 'Stg2' AND C5(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C1(4) = IF C2(2) EQ 'Stg2' AND C1(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C2(4) = IF C3(2) EQ 'Stg2' AND C2(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C3(4) = IF C4(2) EQ 'Stg2' AND C3(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C4(4) = IF C5(2) EQ 'Stg2' AND C4(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
RECAP C5(4) = IF C6(2) EQ 'Stg2' AND C5(1) EQ 'Stg1' THEN 1 ELSE 0;
END
I will send a PM containing a more felexible approach when I have time. T
| |||||||||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |