How to derived a field based on 4 fields

[rishikeshtiwari]

Hi Guys,

I have a Project Details  table.In this table we are maintaining Employee,Project & Submitted weekly time sheets.

Yellow Column are calculated fields. In Excel there is a formula for the yellow columns [=COUNT(C3:F3)] and =[COUNTIF(C3:F3,4)+COUNTIF(C3:F3,5)].

Logic behind calculation of Total Expected Occurrances for 05/July-11/July to  26/July-01/August .

Suppose in First row of 05/July-11/July to  26/July-01/August (4 Columns)if any number is found then it will return 1 ,if it will get NA then return 0. Like first row of field "Total Expected Occurances for July" is 4. Same for second row.

It got 0 only one time so it returns value 1.

For "Occurances >= 4" field if it got 4 or greater than 4 then it returns 1 otherwise 0.

In first row of 05/July-11/July to  26/July-01/August fields its result is 4 because it got 4 four times.

I second row it does't get 4 so  it returns 0.

So I want these results in Qlikview.Would appreciate if you can suggest any idea how we can handle

it in backend script.

Thanks in Advance

Rishi

[#2]

Hi rishi,

you can try like this

Customer:
Mapping Load *
Inline
[
ID, Name
CID001, Customer001
CID002, Customer002
CID003, Customer003
CID004, Customer004
];

SALE:
Load * Inline
[
Customer_ID, Product, QTY
CID001, A, 100
CID002, B, 200
CID001, B, 150
CID003, C, 400
CID004, 4, 300
CID003, B, 200
CID005, 2, 300
];

FINAL:
Load
Customer_ID,
if(Num#(Product),0,1) as Product,

ApplyMap('Customer',Customer_ID,'UNKNOWN') as Name Resident SALE;

Drop Table SALE;

if you see product column  if(Num#(Product),0,1) as Product, - iam checking if its a number ...