Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
@karthik_nataraj can you share the Input Table ? is it two column ?
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)
@karthik_nataraj The input like this ?
and if operation_status is Active return all value ?
Yes input is as shown above
@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: