Table join and group by problem

Fight4Freedom — Wed, 18 Dec 2024 14:04:39 GMT

First of all, hello everyone. I'm new to Qlik and I couldn't get out of the problem inside.

I have one table: Fact. Then I created the Transaction_Number table and added two sum fields using group by and it worked at first.

However, when I use a date filter on the chart, the calculations do not work correctly because I cannot create a key between the two tables.

When I create a unique key as follows and add it both tables, I get an "invalid expression" error.

(Region Code"&'_'&"Branch Code"&'_'&"Completed + Cancel" as %KEY)

Moreover, while I want the group by command to be applied only to fields that contains sum, but it also applies group by to other fields I add to the table, and the calculation gets confused.

Here is the code. I request your kind help.

FACT:

LOAD

Year("Transaction Period") as Year,

Month("Transaction Period") as Month,

Date(MakeDate(Year("Transaction Period") ,num(Month("Transaction Period")), 'YYYY.MM') as Period,

"Region code",

"Branch Code",

"Branch Name",

"Region Name"

"Section Name",

"Process Name"

"Total Refund",

"Completed + Cancel",

"Return Rate"

FROM [………………………………);

TRANSACTION_NUMBER:

LOAD

Sum("Completed + Cancel") as Number of Transactions,

Sum("Total Refund")/Sum("Completed + Cancel") as Refund_Rate

Resident FACT

Group by "Region Name"

;

Re: Table join and group by problem

marcus_sommer — Wed, 18 Dec 2024 14:18:27 GMT

You need to include the grouping-fields also within the aggregation load because otherwise you creates just one global value - in your case in may look like:

LOAD Key,

Sum("Completed + Cancel") as Number of Transactions,

Sum("Total Refund")/Sum("Completed + Cancel") as Refund_Rate

Resident FACT

Group by "Key"

Beside this it might be more appropriate not to aggregate this kind of measures within the script else doing it within the UI which aligns the calculations to the selections and if you want to ignore some dimensionality in your calculation you could use the TOTAL <optional dimensions> statement.

topic Table join and group by problem in QlikView

Table join and group by problem

Re: Table join and group by problem