Suggest

[#1]

After I did some kind of transfarmations using left join and creating flags my table is look like this.

Now am trying to create another flag based on the F1.

Load only first value based on the empno where F1=1

[PrashantSangle]

I did not get you??

F1 is expression??

Regards,

Prashant

[tamilarasu]

Like this?

Data:
LOAD * INLINE [
    EmpNo, DOJ, Emps, F1
    10, 1/1/2017, 1, 1
    10, 1/1/2017, 2, 1
    10, 1/1/2017, 3, 1
    20, 2/1/2017, 1, 0
    20, 2/1/2017, 2, 0
    20, 2/1/2017, 3, 0
    30, 3/1/2017, 1, 0
    30, 3/1/2017, 2, 0
    30, 3/1/2017, 3, 0
    40, 4/1/2017, 4, 1
    40, 4/1/2017, 5, 1
    40, 4/1/2017, 6, 1
    50, 5/1/2017, 4, 0
    50, 5/1/2017, 5, 0
    50, 5/1/2017, 6, 0
];

Result:
Load *,
If(EmpNo<>Peek('EmpNo') And F1 =1,1,0) as Flag
Resident Data Order by EmpNo, Emps;

DROP Table Data;

[#4]

I tell u my exact requirement

I have emp table and it contains

Company code,emp code, DOJ,

And requisition table it contains :company code

Requisition number, requisition date, Target date,num of employees

And inducted table contains

Company code, requisition number, emp code

These three tables I have

Now my requirement is

Dimensions>requisition number, requisition date and target date,

I want to calculate total head count and

Employee count if DOJ<=targetdate

And employee count DOJ>targetdate

These three I want to show in straight table.

For head count it's working fine sum(distinct num of employees)

But I strucked in remaining expressions.

Why because in the back end, I have used left joins between requisition and inducted tables and between requisition and employee table.

So that am not getting accurate results.

Any suggestions???

Am also sharing snapshots of the this scenarioplease find for your reference.