Row selection in if statement

[karthik_nataraj]

Hi All, 

I need to create an if statement in pivot table for the below condition, can anyone help me on this?

If(Operation_status='Planned', then I need only first row from planned table)

Input		Output required
operation_status
planned	Cricket		Planned	Cricket
	Football
	Throwball
	Swimming
	Cycling
Active	Football		Active	Football
	Throwball
	Swimming
	Cycling

[Taoufiq_Zarra]

@karthik_nataraj  can you share the Input Table ? is it two column ?

[karthik_nataraj]

Hi,

Thanks for your reply, Please find the below input table fyr.

Input
operation_status	operation_description
planned	Cricket
	Football
	Throwball
	Swimming
	Cycling
Active	Football
	Throwball
	Swimming
	Cycling

if operation_status is planned then by default my operation_description should be first row(i.e. cricket)  

[Taoufiq_Zarra]

@karthik_nataraj  The input like this ?

and if operation_status is  Active return all value ?

[karthik_nataraj]

Yes input is as shown above

[Taoufiq_Zarra]

@karthik_nataraj  you can for example (If I understood correctly) :

Data:

LOAD *,if(rowno()=1,1,if(peek(operation_status)=operation_status,peek(IDtmp)+1,1)) as IDtmp,rowno() as IDRank INLINE [
    operation_status, operation_description
    planned, Cricket
    planned, Football
    planned, Throwball
    planned, Swimming
    planned, Cycling
    Active, Football
    Active, Throwball
    Active, Swimming
    Active, Cycling
];


output:
noconcatenate

load * resident Data where operation_status='planned' and IDtmp=1;
load * resident Data where Match(operation_status,'planned')=0;

drop table Data;
drop fields IDtmp,IDRank;

output:

this approch allow you also if you have more than one planned like :

Data:

LOAD *,if(rowno()=1,1,if(peek(operation_status)=operation_status,peek(IDtmp)+1,1)) as IDtmp,rowno() as IDRank INLINE [
    operation_status, operation_description
    planned, Cricket
    planned, Football
    planned, Throwball
    planned, Swimming
    planned, Cycling
    Active, Football
    Active, Throwball
    Active, Swimming
    Active, Cycling
    planned, Football
    planned, Throwball
    planned, Swimming
    planned, Cycling
];


output:
noconcatenate

load * resident Data where operation_status='planned' and IDtmp=1;
load * resident Data where Match(operation_status,'planned')=0;

drop table Data;
drop fields IDtmp;

output: