Skip to main content
Question

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

  • February 29, 2024
  • 2 replies
  • 11 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
    • Sverre Klein
    • Level 10
    • 204 replies
    • March 1, 2024

    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
    • dennis.pflug
    • Level 10
    • 182 replies
    • March 1, 2024

    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