I need to display a report with more than 5 columns which should satisfy conditions like
column 01 : It should display the count of checks in a restaurant where the check open mins are less than 5
column 02 : It should display the count of checks in a restaurant where the check open mins are greater than equal to 5 and less than 30
In this way I need to satisfy different conditions
I tried using compute
COMPUTE COPEN5/I5 = IF TABLETURN.TABLETURN.CHECKOPENMINS LT 5 THEN CNT.TABLETURN.TABLETURN.CHECKOPENMINS + 1
How to use compute for other conditions?
How to display them as different columns in a report?

Report

This message has been edited. Last edited by: FP Mod Chuck,

This is sort of basic coding skills and you already have your pseudo-code

column 01 : It should display the count of checks in a restaurant where the check open mins are less than 5
column 02 : It should display the count of checks in a restaurant where the check open mins are greater than equal to 5 and less than 30

The following will probably not return the good numbers

COMPUTE COPEN5/I5 = IF TABLETURN.TABLETURN.CHECKOPENMINS LT 5 THEN CNT.TABLETURN.TABLETURN.CHECKOPENMINS + 1

since you cannot count (CNT) the same field that, in this case you test LT 5. I understand that you want to count those LT 5, but you cannot do it that way.
You are missing an ELSE and have to understand that a COMPUTE is applied on already selected date (once the WHERE clause are done). So you may have to test each row to determine in which condition to put in.

From what I understand you should better have your fields DEFINEd such as

DEFINE FILE abc
COL1 /P5 = IF CHECKOPENMINS LT 5 THEN 1 ELSE 0;
COL2 /P5 = IF CHECKOPENMINS GE 5 AND CHECKOPENMINS LT 30 THEN 1 ELSE 0;
COL3 /P5 = IF CHECKOPENMINS GE 30 THEN 1 ELSE 0;
END
TABLE FILE abc
SUM COL1
    COL2
    COL3
BY COL_TO_ORDER
WHERE COL_TO_TEST EQ 'COND_X';
END

So by using define instead of Compute how can I change the count of a field that satisfies those conditions

quote:

Originally posted by MartinY:
This is sort of basic coding skills and you already have your pseudo-code

column 01 : It should display the count of checks in a restaurant where the check open mins are less than 5
column 02 : It should display the count of checks in a restaurant where the check open mins are greater than equal to 5 and less than 30

The following will probably not return the good numbers

COMPUTE COPEN5/I5 = IF TABLETURN.TABLETURN.CHECKOPENMINS LT 5 THEN CNT.TABLETURN.TABLETURN.CHECKOPENMINS + 1

since you cannot count (CNT) the same field that, in this case you test LT 5. I understand that you want to count those LT 5, but you cannot do it that way.
You are missing an ELSE and have to understand that a COMPUTE is applied on already selected date (once the WHERE clause are done). So you may have to test each row to determine in which condition to put in.

From what I understand you should better have your fields DEFINEd such as

DEFINE FILE abc
COL1 /P5 = IF CHECKOPENMINS LT 5 THEN 1 ELSE 0;
COL2 /P5 = IF CHECKOPENMINS GE 5 AND CHECKOPENMINS LT 30 THEN 1 ELSE 0;
COL3 /P5 = IF CHECKOPENMINS GE 30 THEN 1 ELSE 0;
END
TABLE FILE abc
SUM COL1
    COL2
    COL3
BY COL_TO_ORDER
WHERE COL_TO_TEST EQ 'COND_X';
END

Nihita,

I already gave you the solution.

COL1 will be set to 1 if the CHECKOPENMINS is less than 5
COL2 will be set to 1 if the CHECKOPENMINS is greater then or equal to 5 and less than 30
COL3 will be set to 1 if the CHECKOPENMINS is greater then or equal to 30

Then by doing

SUM COL1 AS 'Lt 5'
    COL2 AS '5 - 29'
    COL3 AS '30+'
BY COL_TO_ORDER

You will have the count of CHECKOPENMINS per defined groups grouped by COL_TO_ORDER

Thanks for the reply. But I need the count of another dimension(count of checknumbers) not the count of checkopenmins. I need the count of checknumbers whose check open mins are less than 5 and so on

quote:

Originally posted by MartinY:
Nihita,

I already gave you the solution.

COL1 will be set to 1 if the CHECKOPENMINS is less than 5
COL2 will be set to 1 if the CHECKOPENMINS is greater then or equal to 5 and less than 30
COL3 will be set to 1 if the CHECKOPENMINS is greater then or equal to 30

Then by doing

SUM COL1 AS 'Lt 5'
    COL2 AS '5 - 29'
    COL3 AS '30+'
BY COL_TO_ORDER

You will have the count of CHECKOPENMINS per defined groups grouped by COL_TO_ORDER

Hi Nihitha

Martin's got you on the right track I think.

Is there something you don't quite understand there?

Looking at your sample report, you'll need to add your extra columns following Martins example. Including just one more column that you could call 'total' that always has a 1 in in it.

then add a COMPUTE for the percentage following the COL1 and so forth to give you the pair of the count and then the percentage.

Just add a BY LOCATION to get your table name in the first column and I think you'll be set.

I couldn't tell for sure in your opening question, but if you think you'll need those DEFINE's in other reports, you can put them in their own focexec and -INCLUDE it when you need it, or add them to your Master file.

If we're not on track here, it's likely we don't understand your data. Maybe if you put up a few rows for us to see, we can help out more.

Out of curiousity, what company do you work for? I'm trying to think of who does restaurant business. All that comes to mind is Rosnet here in the Kansas City area.

Good luck!
Toby

Nihitha,

You already have your own answer :

quote:

I need the count of checknumbers whose check open mins are less than 5 and so on

But I don't know your data and what you need, so something similar to this should work

TABLE FILE abc
PRINT CHECKOPENMINS
BY CHECKNUMBERS
BY COL_TO_ORDER
WHERE COL_TO_TEST EQ 'COND_X';
ON TABLE HOLD AS TMP
END
-RUN

DEFINE FILE TMP
COL1 /P5 = IF CHECKOPENMINS LT 5 THEN (IF CHECKNUMBERS EQ LAST CHECKNUMBERS THEN COL1 + 1 ELSE 1) ELSE 0;
COL2 /P5 = IF CHECKOPENMINS GE 5 AND CHECKOPENMINS LT 30 THEN (IF CHECKNUMBERS EQ LAST CHECKNUMBERS THEN COL1 + 1 ELSE 1) ELSE 0;
COL3 /P5 = IF CHECKOPENMINS GE 30 THEN (IF CHECKNUMBERS EQ LAST CHECKNUMBERS THEN COL1 + 1 ELSE 1) ELSE 0;
END
TABLE FILE TMP
SUM COL1
    COL2
    COL3
BY COL_TO_ORDER
END

But depending on how your data exist, it may be possible to perform it in one step or in a different way. Most of the time, there is more than one way to accomplish the same thing.

A small example using the CAR file

  
-* File nihitha01.fex
DEFINE FILE CAR
COL1/I3=IF SALES LT 5000 THEN 1 ELSE 0;
COL2/I3=IF SALES GE 5000 AND SALES LT 10000 THEN 1 ELSE 0;
COL3/I3=IF SALES GE 10000 AND SALES LT 20000 THEN 1 ELSE 0;
COL4/I3=IF SALES GE 20000 THEN 1 ELSE 0;
END
TABLE FILE CAR
SUM COL1 COL2 COL3 COL4
BY COUNTRY
END