Skip to main content
Question

Filter on cases where LAST entry (document) meets specific conditions

  • February 29, 2024
  • 2 replies
  • 20 views
felix.keite12
Level 1

Dear community,

I need your help with pql. I want to create a table which should display only the LAST document for each case (based on Document creation date). The documents table is directly linked to the casetable.

 

I tried with component filter but syntax error is appearing:

FILTER PU_LAST ( CaseTable.CaseID, Documents.Technical_ID [, ORDER BY Documents.CreationDate DESC)

 

Based on the last entry/document for each case I have additional filterings like "documents.type" = 'A' etc. - but even when deleting the further condtions the querry is not working.

 

Do you have an idea? Thanks in advance!

Best Felix

    2 replies

    Sverre Klein
    Level 10
    Forum|alt.badge.img+14

    Hi @felix.keite12,

     

    I'm not a king in PU functions so bear with me here. I'm still learning 😉

     

    I don't think you should use a filter on your table, but an KPI. Try this one.

     

    PU_LAST ("CaseTable", "Documents"."Technical_ID", "Documents"."Type" = 'A', ORDER BY Documents.CreationDate DESC)

     

    The filter expression "Documents"."Type" = 'A' can be changed or deleted if you want!

     

    I have tried to replicate it in my sandbox, this is what I got.

     

    image 

    Case ID is from my Case Table. (dimension)

     

    Last Activity is the last activity that occurred in that case (KPI). --> PQL:

    PU_LAST ("CASE_TABLE_HUBSPOT", "ACTIVITIES_TABLE_HUBSPOT"."ACTIVITY_NAME", ORDER BY "ACTIVITIES_TABLE_HUBSPOT"."EVENT_TIME" DESC)

     

    Hopefully this helped you out.

     

    Kind regards,

    Sverre Klein

    dennis.pflug
    Level 10

    Hi,

     

    the FILTER doesn't work as the filter condition doesn't know what to filter.

    The result for your PU_LAST function will be the Technical_ID per Case.

    Let say one of your Technical_IDs would be 12345 in this case your filter looks like this

     

    FILTER '12345'

     

    So for use cases where you need to add PU_ Functions into a Filter you need to have condition like

    FILTER PU_LAST(CASE, Technical_ID) = '12345'

     

    which would result in FILTER 12345 = 12345 for some cases and this will give you a result at the end.

     

    BR

    Dennis

     

     

    Community Code of ConductAccessibility statement