Count function in load editor

[Luffy20]

Hi all,

I have written the below expression in chart visualization

if((len(Trim(ABC))=0 or ABC=0),(count(distinct([XYZ])))/2,ABC)

I have to write this in Load editor and rename the expression to field

ABC is present in one QVD and XYZ is Present in Another QVD

I have Written the following script in Load Editor but it is showing as invalid expression

Temp:

Load
RED,
[XYZ]

FROM [$(vDataPath)/Data_QVD/tory.qvd]
(qvd);

Join (Temp)

Load

RED,

ABC

FROM [$(vDataPath)/Data_QVD/Fact.qvd]
(qvd);

Temp2:

Load

RED,

ABC,

[XYZ],
if((len(Trim(ABC))=0 or ABC=0),(count(distinct([XYZ])))/2,ABC) as New_Field
Resident Temp;
Drop Table Temp;

Can you help me with this

[diegozecchini]

Hi! I think we need some adjustments. The key issue here is that the count(distinct([XYZ])) function cannot be directly used in the Load statement in this way, as aggregation functions are typically not allowed in the Load script without a Group By clause.

Here’s how you can rewrite your script to achieve the desired result:

Load the two QVDs and join them as you’ve done.
Use an aggregated load with Group By to handle the count(distinct([XYZ])) part.
Here’s the modified script:

Temp:
Load
RED,
[XYZ]
FROM [$(vDataPath)/Data_QVD/tory.qvd]
(qvd);

Join (Temp)
Load
RED,
ABC
FROM [$(vDataPath)/Data_QVD/Fact.qvd]
(qvd);

Temp2:
Load
RED,
ABC,
// Calculate count of distinct XYZ for each RED group
if(len(trim(ABC)) = 0 or ABC = 0, CountDistinctXYZ / 2, ABC) as New_Field
Resident Temp
Group By RED, ABC;

Temp_Count:
Load
RED,
Count(distinct [XYZ]) as CountDistinctXYZ
Resident Temp
Group By RED;

Left Join (Temp2)
Load
RED,
CountDistinctXYZ
Resident Temp_Count;

Drop Table Temp;
Drop Table Temp_Count;

Count Calculation: We add a temporary table, Temp_Count, to calculate Count(distinct [XYZ]) for each RED group, and then join it back to Temp2.
Field Calculation: The if condition is then applied to either use ABC or half of CountDistinctXYZ depending on the ABC value.
Dropping Temporary Tables: After obtaining the desired field (New_Field), we drop the Temp and Temp_Count tables.

This approach should provide a valid expression for New_Field.

Hope it helps,

regards,

Diego

[Luffy20]

Hi Diego ,

it worked ,but i am getting synthetic keys and i am Renaming RED but it is not woking.

Can you help me with this

[diegozecchini]

Hi! Synthetic keys are typically created when there are multiple fields with identical names across different tables. Qlik automatically tries to associate them, but if you’re seeing unexpected synthetic keys, it’s likely because the fields RED and possibly other columns are being repeated in the joined tables.To solve it you may want to rename the RED field (or any other common fields) consistently across the different loads to avoid Qlik’s automatic association.
An other solution would be a bit more complicated, but if could you do renaming would be easier.

[Luffy20]

I am renaming them as follows,but it was not working

Temp:

Load
RED as RED1,
[XYZ]

FROM [$(vDataPath)/Data_QVD/tory.qvd]
(qvd);

Join (Temp)

Load

RED asRED1,
ABC

FROM [$(vDataPath)/Data_QVD/Fact.qvd]
(qvd);

Temp2:

Load

RED asRED1,
ABC,
IF((len(Trim(ABC))='0' or ABC='0'),(count(DISTINCT(XYZ)))/2,ABC) as Emission
Resident Temp
group by RED1,ABC;

Temp_Count:
Load
RED as RED1
Count(DISTINCT(XYZ)) as Count
Resident Temp
Group By RED1;

Left Join (Temp2)
Load
RED as RED1,
Count
Resident Temp_Count;

Drop Table Temp;
Drop Table Temp_Count;

can you help me with this

[diegozecchini]

It looks like the issue may stem from small syntax issues, such as spacing or field name typos, try this script

Temp:
Load
RED as RED1,
[XYZ]
FROM [$(vDataPath)/Data_QVD/tory.qvd]
(qvd);

Join (Temp)
Load
RED as RED1,
ABC
FROM [$(vDataPath)/Data_QVD/Fact.qvd]
(qvd);

Temp2:
Load
RED1,
ABC,
if(len(trim(ABC)) = 0 or ABC = 0, CountDistinctXYZ / 2, ABC) as Emission
Resident Temp
Group By RED1, ABC;

Temp_Count:
Load
RED1,
Count(distinct [XYZ]) as CountDistinctXYZ
Resident Temp
Group By RED1;

Left Join (Temp2)
Load
RED1,
CountDistinctXYZ
Resident Temp_Count;

Drop Table Temp;
Drop Table Temp_Count;

[Luffy20]

Yes i have tried this and it is working and synthetic keys are removed ,

but in visualization while using this filed the values are not matching properly

[Luffy20]

Renaming the fields is not working , is there any other way to remove synthetic keys

[diegozecchini · Accepted solution]

Hi, I put some comments on first post, you could try this to avoid synthetic keys

Temp1:
Load
RED as RED_Key, // Renaming to avoid synthetic keys
[XYZ]
FROM [$(vDataPath)/Data_QVD/tory.qvd]
(qvd);

Join (Temp1)
Load
RED as RED_Key, // Renaming to match the joined table
ABC
FROM [$(vDataPath)/Data_QVD/Fact.qvd]
(qvd);

Temp2:
Load
RED_Key, // Using the renamed field
ABC,
// Calculate count of distinct XYZ for each RED group
if(len(trim(ABC)) = 0 or ABC = 0, CountDistinctXYZ / 2, ABC) as New_Field
Resident Temp1
Group By RED_Key, ABC;

Temp_Count:
Load
RED_Key, // Using the renamed field for consistent joining
Count(distinct [XYZ]) as CountDistinctXYZ
Resident Temp1
Group By RED_Key;

Left Join (Temp2)
Load
RED_Key,
CountDistinctXYZ
Resident Temp_Count;

Drop Table Temp1;
Drop Table Temp_Count;

Other solution is to change the fields name on the source if possibile.

[diegozecchini]

glad it helped