Can you help me get this table from the following query?

[dhirendra761]

HI All,

Below Query:

| convert ctime(_time) AS Date timeformat="%d/%m/%y" 
| eval File_Copied=case(File_Copied="NatCo 2","GE",File_Copied="natco 3","FR") 
| eval File_Created=case(File_Created="natco 2","GE",File_Created="NatCo 3","FR") 
| chart count(File_Copied) over Date  by File_Created | untable Date,File_Created,count
 |eval count=if(count>0,"OK","KO")|maketable Date,File_Created,count

generates:

 Date   |FR |GE
16/11/18|   OK  |KO
17/11/18|   KO  |OK
18/11/18|   KO  |KO

Now, I want blanks space entry when date is sunday or saturday and value is KO as below table:

Date    |FR |GE
    16/11/18|   OK  |KO
    17/11/18|         |OK
    18/11/18|         |

I have field day_wday, which have "sunday,saturday,friday values"
Can you please help me to get this table from my query.

Thank You.

[gcusello]

Hi dhirendra761,
did you tried to add at the end of your search an eval condition like the following:

| eval count=if((day_wday="saturday" OR day_wday="sunday") AND count="KO"," ",count)

Bye.
Giuseppe

[dhirendra761]

Hi @Anonymous As you suggested, I applied on search but didn't get the expected result:
| convert ctime(_time) AS Date timeformat="%d/%m/%y"
| eval File_Copied=case(File_Copied="NatCo 1","GE",File_Copied="NatCo 2","SP",File_Copied="NatCo 4","UK",File_Copied="natco 3","FR")
| eval File_Created=case(File_Created="natco 1","GE",File_Created="natco 2","SP",File_Created="natco 4","UK",File_Created="NatCo 3","FR")
| chart count(File_Copied) over Date by File_Created

| untable Date,File_Created,count 
| eval count=if(count>0,"OK","KO") | eval count=if((day_wday="saturday" OR day_wday="sunday") AND count="KO"," ",count)
| maketable Date,File_Created,count

Can you please suggest something else.

[dhirendra761]

There were saturday and sunday on 17th and 18th.

[dhirendra761]

I think I should not use Chart command for core-relation of table. Is there any other command which works as chart and gives correlated result between rows and columns.

[dhirendra761]

@somesoni2 @woodcock