CODEWITHSUNDEEP
xquerymarklogicmarklogic-optic-api
Rashmita Purkayastha
Rashmita Purkayastha

Reputation: 449

Error in MarkLogic Optic Query - to get percentage of occurrence of a record in a template view document

I am trying to find the percentage of a certain record based on a condition to the total no of that record . I am using optic query to get the result.

To be specific on the requirement :

Give a percentage of a certain record, when there is a condition, with the total number of that record available in the template view.

Here is my optic query which I am trying -

xquery version "1.0-ml";
import module namespace op="http://marklogic.com/optic" at "/MarkLogic/optic.xqy";
import module namespace ofn="http://marklogic.com/optic/expression/fn" at "/MarkLogic/optic/optic-fn.xqy";
import module namespace osql="http://marklogic.com/optic/expression/sql" at "/MarkLogic/optic/optic-sql.xqy";  
declare option xdmp:mapping "false";
let $view := op:from-view("GTM2_Shipment", "Shipment_View")
let $Ancillary_QuotePrice := op:view-col("Shipment_View", "Ancillary_QuotePrice")
return $view
=> op:group-by((),(op:count("TotcountOfColumn", $Ancillary_QuotePrice),op:count("percentageCount", $Ancillary_QuotePrice)))
=> op:select(
op:as("CountOnCond", op:where(op:and((
                   op:gt($Ancillary_QuotePrice, 0),op:gt(ofn:format-dateTime(op:col('BookingCreateDt'), '[Y0001]-[M01]-[D01]'),osql:dateadd('month',-6, ofn:format-dateTime(fn:current-dateTime(),'[Y0001]-[M01]-[D01]')))

                   ))
                   )
      ),
op:as("Percentage",op:divide(op:col("CountOnCond"),op:col("TotcountOfColumn"))) 
             )    
=> op:result()

It gives me this error -

1.0-ml] XDMP-TOOFEWARGS: (err:XPST0017) op:where(op:and((op:gt($countOfColumn, 0), op:gt(ofn:format-dateTime(op:col("BookingCreateDt"), "[Y0001]-[M01]-[D01]"), osql:dateadd("month", -6, ofn:format-dateTime(fn:current-dateTime(), "[Y0001]-[M01]-[D01]")))))) -- Too few args, expected 2 but got 1

Also please check if my query logic is correct or not.

xquery version "1.0-ml";
import module namespace op="http://marklogic.com/optic" at "/MarkLogic/optic.xqy";
import module namespace ofn="http://marklogic.com/optic/expression/fn" at "/MarkLogic/optic/optic-fn.xqy";
import module namespace osql="http://marklogic.com/optic/expression/sql" at "/MarkLogic/optic/optic-sql.xqy";  
declare option xdmp:mapping "false";
let $view := op:from-view("GTM2_Shipment", "Shipment_View")
let $Var1 := op:view-col("Shipment_View", "Ancillary_QuotePrice")
let $Var2 := op:view-col("Shipment_View", "Shipment_Ref")
return $view

=> op:group-by((),(op:count("Var2", $Var2),op:count("Var1", $Var1)))
=>op:select(op:as("multiply", op:divide(op:col("Var1"), op:col("Var2"))))
=>op:select(op:as("percentageFinal", op:multiply(100, op:col("multiply"))))
=> op:result()

Above is the straightforward percentage found for Var1 with total of Var2. I want the condition of the Var1 to be added in the code. Where can I add the where condition please.

Upvotes: 1

Views: 53

Answers (1)

Mads Hansen
Mads Hansen

Reputation: 66781

op:where() expect the first parameter to be the $plan. Typically, when chaining calls from a plan using => it is set as that first parameter for you, and you are then providing the subsequent parameters starting from the second parameter with what is explicitly set in the function call.

So, when attempting to apply op:where() as the expression for op:as() to bind to:

op:as("CountOnCond", op:where(op:and((
               op:gt($Ancillary_QuotePrice, 0),op:gt(ofn:format-dateTime(op:col('BookingCreateDt'), '[Y0001]-[M01]-[D01]'),osql:dateadd('month',-6, ofn:format-dateTime(fn:current-dateTime(),'[Y0001]-[M01]-[D01]')))

               ))
               )

the op:and() parameter being specified is the one and only parameter being specified, but op:where() has two required parameters.

That is why it throws the XDMP-TOOFEWARGS error.

Upvotes: 0

Related Questions