Conditional Nested If Statement

[Mary666]

Hello,

I have been reading all the blogs  around this subject, some questions I have had answered, but in this case I am not sure how to approach it.  Scenario: 

Three fields:

1. RecordStage, 2. pdfRecord 3. csvRecord

The RecordStage is a field I have created that has all the values I need. I just want to know the following:

If RecordStage=0 display 0

If RecoredStage>1 indicate if its logged in the pdfRecord  or in the csvRecord by indicating, Yes or No, All for being logged in both. 

(index="xyz" ) OR (index="123"  ) 

| stats values(compGen) as compGen  values(levels) as levels count(eval(like(level,"RecordStage%"))) AS RecordStage values( Result) as  Result by TextDoc

| eval Reslut =if(RecordStage=0, "0"  AND (RecordStage=>1 AND RecordStage=pdfRecord OR RecordStage=csvRecord ), "Yes", "No", "In Both Fields"))

[scelikok]

@Mary666 , sorry, I am lost after first eval command, maybe because your anonymizations. 

| eval PDF=case(level="RecordStageX", pdfRecord),  CSV=case(level="RecordStageX", csvRecord)

 Your is statements seems fine but the problem can be counting pdf and csv values. 

[Mary666]

@scelikok  No worries, I'm sorry for the confusion. I was doing some more work on this and I think your first proposition for the solution may work.

| eval Reslut =case(RecordStage=0,"0",RecordStage=1,"Yes",1=1,"In Both Fields")

The only question about the above query is that I dont see how its telling if RecordStage is being logged in either pdfRecord or csvRecord. I added the two fields below in the the index=123 pdfRecord=* ad csvRecord=* 

But I would still like to know if:

If RecordStage=0 display 0

If RecoredStage>1 indicate if its logged in the pdfRecord  or in the csvRecord by indicating, Yes or No, All for being logged in both

At this point any direction you can point me to I would appreciate it. Thanks in advance. 

(index="xyz" ) OR (index="123" pdfRecord=* csvRecord=* ) 

| stats values(compGen) as compGen  values(levels) as levels count(eval(like(level,"RecordStage%"))) AS RecordStage values( Result) as  Result by TextDoc

| eval Reslut =case(RecordStage=0,"0",RecordStage=1,"Yes",1=1,"In Both Fields")

[scelikok]

Hi @Mary666,

"case" function seems to help you like below. I couldn't build a full query since the data inside RecordStage is not certain. If you can write a sample data and desired sample output, I can help better.

| eval Reslut =case(RecordStage=0,"0",RecordStage=1,"Yes",1=1,"In Both Fields")

[Mary666]

@scelikok  Thanks for your help. I really apricate your time. Here is my sample data.  I am having difficulty with my if statement, not sure if you would still recommend case. I re-wrote my query a bit. I would like to display:

If PDF or CSV have a zero count for RecordStageX then = "NULL"

If PDF>CSV = "No "  (This will tell me if RecordStageX is missing from wither PDF or CSV)

If PDF=CSV = "Yes" (This will just meant that RecordStageX is being logged in both PDF or CSV)

I cant seem to get my if statement to give me the desired Results: 

| eval Testing =if(PDF="0" OR CSV="0", "Null", if(( PDF>CSV), "NO", if((PDF=CSV) "Yes"))))

(index="xyz" ) OR (index="123"  ) 

| eval PDF=case(level="RecordStageX", pdfRecord),  CSV=case(level="RecordStageX", csvRecord) 
| eval Testing =if(PDF="0" OR CSV="0", "Null", if(( PDF>CSV), "NO", if((PDF=CSV) "Yes"))))
| stats values(compGen) as compGen  values(levels) as levels count(eval(like(level,"RecordStageX%"))) AS RecordStage values( Tesing) as  Testing by TextDoc

Desired output: 

TextDoc	CompGen	level	RecordStageX	Testing
TextDoc122	False	RecordStageX	20	No
TextDoc123	True	RecordStageX	24	Yes