topic Re: "Translating" from (SQL statement in) Crystal in QlikView

"Translating" from (SQL statement in) Crystal

datanibbler — Wed, 17 Apr 2013 13:41:20 GMT

Hi,

I am trying to redo a report existing in Crystal in QlikView and I'm having difficulties interpreting a formula. I have looked into the QlikView help and looked it up online, but I cannot quite make sense of it - the way I understand the QV help on that function makes the syntax look wrong.

Maybe anyone can advise me here.

The original statement is

Maximum
({V_SO_PACKAGE_ITEMS.PACKED_QTY}, {V_SHIPPED_PACKAGES.PACKAGE_CODE})

I cannot make sense of it because, the way I understand the QV help, the MAX function would take two parameters, a value_to_return and a number (like 1, 2, 3) - the first parameter here is a value all right, but the PACKAGE_CODE is a ten-digit ID number, not usable as a rank.

In principle, the field that's calculated here is available in original in the database, but for some reason we cannot get it out, so I want to try with the way Crystal did this.

Can anybody tell me a) what this function does and b) how I can do the same in QlikView?

Thanks a lot!

Best regards,

DataNibbler

Re: "Translating" from (SQL statement in) Crystal

Gysbert_Wassenaar — Wed, 17 Apr 2013 17:51:14 GMT

That's probably the equivalent of using this in the script:

LOAD

V_SHIPPED_PACKAGES.PACKAGE_CODE

max(V_SO_PACKAGE_ITEMS.PACKED_QTY)

FROM ...somesource...

GROUP BY V_SHIPPED_PACKAGES.PACKAGE_CODE

In a chart you'd use V_SHIPPED_PACKAGES.PACKAGE_CODE as dimension and simply max(V_SHIPPED_PACKAGES.PACKAGE_CODE) as expression

Re: "Translating" from (SQL statement in) Crystal

datanibbler — Thu, 18 Apr 2013 06:53:44 GMT

Hi Gysbert,

Thanks! So that would generate groups where every line has the same PACKAGE_CODE and load the largest numeric value of PACKED_QTY, right?

Best regards,

DataNibbler