1

How to mention the NOT IN clause for a date filter in compact query (not saql)? Here is the section of json with respect to filters

"query": {
            "filters": [
                [
                    "Last Login",
                    [
                        [
                            [
                                "month",
                                -6
                            ],
                            [
                                "month",
                                0
                            ]
                        ]
                    ],
                    ">=<="
                ]
            ],
            "measures": [
                [
                    "count",
                    "*"
                ]
            ]
        }

These come up when i add the relative filter on Last Login Date (current month to 6 months ago). By default, the IN operator is considered. What should i do add NOT IN clause?

The alternative to this is SAQL, but with that i lose the ability to dynamically change filters using date selector.

1

Hopefully this will provide additional context. The best way to do this is through SAQL as it will allow you to better customize your query. In order to also allow your query to change using the date selector you will need to find the name of the step that is your date selector. I have included an example below that hopefully will help better answer your question.

I have also found this SAQL Reference guide super helpful: https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_operators_logical.htm

"query": {
      "pigql": "q = load \"Dataset_Name\"; q = filter q by 'Last Login Date' ! in {{ selection(Date_Selector) }}; q = foreach q generate 'Last Login Date' as 'Last Login Date', 'Employee Name' as 'Employee Name';",  
      "filters": [
            [
                "Last Login",
                [
                    [
                        [
                            "month",
                            -6
                        ],
                        [
                            "month",
                            0
                        ]
                    ]
                ],
                ">=<="
            ]
        ],
        "measures": [
            [
                "count",
                "*"
            ]
        ]
    }
0

Having played around a little, I haven't identified a way to do this in the compact form. Assuming I have understood your requirement, you could achieve this using SAQL by inserting a results binding like this:

q = filter q by 'Last Login' not in {{ results(siteSelector)}};.

  • It looks like SaQL is the only way – SF Ninja Jun 15 '16 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.