Dear Qlik Support Team,

We would like to clarify a question regarding update behavior when transformations (such as TRIM) are applied in Qlik Replicate.

Scenario:
If a value in column A1 of table A in the source journal is updated (e.g., from "A " to "B"), and Qlik Replicate applies a transformation (e.g., TRIM) before writing to the target Oracle database, what value does Qlik use in its update condition — the original source value "A " or the transformed value "A"?

Currently, we have confirmed that the target Oracle table may have been manually updated. We also noticed the following message:

"Source changes that would have no impact were not applied to the target database. Refer to the 'attrep_apply_exceptions' table for details."

When reviewing the attrep_apply_exceptions table, we see that the UPDATE statements include WHERE clauses based on the original source data (from AS400).

Given this, we would like to confirm:

• If the target Oracle table already contains a trimmed version of the data (e.g., "A"), and the source update is based on "A " (with a trailing space), will Qlik still be able to match the record and apply the update?

• Or would this cause a mismatch due to the transformation, thus preventing the WHERE condition from finding the correct target row?

We appreciate your help in clarifying this behavior so we can better understand how Qlik handles updates when transformations are involved.

Looking forward to your guidance.

Hi All,

We are currently facing challenges with the increasing size of one of our Qlik Cloud applications. Below are the key details and constraints:

• Initial App Design:
  The app was originally designed with data aggregated at the monthly level, resulting in an app size of approximately 1.5 GB.

• Requirement Change:
  A few months later, a requirement emerged to analyze data at the daily level. After this change, the app size increased to ~3 GB.

• Recent Change – Ageing Buckets:
  In a recent enhancement, we introduced ageing buckets in a large transactional table.

  • Prior to bucketing: ~5 crore (50 million) rows

  • After bucketing: ~13 crore (130 million) rows

  • Current app size: ~7 GB

• Data Volume:
  We are only loading 3 years of data in the app.

• Insight Advisor Constraint:
  The app also has Insight Advisor configured.
  We are considering splitting the app into two using document chaining to manage size, but we foresee a major limitation:

   

  If a user queries from the landing sheet using Insight Advisor, it may not support document chaining to the second app, which is a blocker from a user experience standpoint.

  Kindly suggest a feasible approach or workaround to help reduce the app size while considering the above constraints. Specific input on how to retain Insight Advisor functionality in a document-chained environment would be particularly helpful.

  Thanks in advance for your support.

   

Hello!

 I wanted to try to see the difference in app size between using the concatenate method for 3 fact tables with few fields in common and the link table approach. In my example I load 20M rows of each fact table with only 3 common dimensions of 11 total between the tables.

So I made some test data, I'm not sure what conclusions I can make from this, if any, which is why I made this post. Maybe my example does not make sense. But in my example the Link table approach has an app size of  159MB and the concatenate approach an app size of 184MB. 

So the concatenate approach created a larger app which goes against the general idea that the link table approach creates a larger app due to the extra link table. I guess the reason is that I only use 10 distinct values for the Dim1-10 so my link table is small (only 110 rows), maybe this is not realistic.

But I'm still curious why the Concatenate approach created a larger app? Is it because of the null values? I thought the null values had insignificant impact on app size. Is is because the table is wider? 

The code:

---CONCATENATE---

Table1:
LOAD
'2023' as year,
Floor(Rand() * 10) + 1 AS Dim1,
Floor(Rand() * 10) + 1 AS Dim2,
Floor(Rand() * 10) + 1 AS Dim3,
Floor(Rand() * 10) + 1 AS Dim4,
Floor(Rand() * 10) + 1 AS Dim5,
Floor(Rand() * 10) + 1 AS Dim6,
Floor(Rand() * 10) + 1 AS Dim7,
Floor(Rand() * 10) + 1 AS Dim8,
Floor(Rand() * 10) + 1 AS Dim9,
Floor(Rand() * 10) + 1 AS Dim10,
Floor(Rand() * 100) + 1 AS Value1,
'Table1' as Source
AutoGenerate(20000000);

Concatenate(Table1)
LOAD
'2023' as year,
Floor(Rand() * 10) + 1 AS Dim1,
Floor(Rand() * 100) + 1 AS Value2,
'Table2' as Source
AutoGenerate(20000000);

Concatenate(Table1)
LOAD
'2023' as year,
Floor(Rand() * 10) + 1 AS Dim1,
Floor(Rand() * 10) + 1 AS Dim2,
Floor(Rand() * 100) + 1 AS Value3,
'Table3' as Source
AutoGenerate(20000000);

---LINK TABLE---

_Table1:
LOAD
'2023' as Year,
Floor(Rand() * 10) + 1 AS Dim1,
Floor(Rand() * 10) + 1 AS Dim2,
Floor(Rand() * 10) + 1 AS Dim3,
Floor(Rand() * 10) + 1 AS Dim4,
Floor(Rand() * 10) + 1 AS Dim5,
Floor(Rand() * 10) + 1 AS Dim6,
Floor(Rand() * 10) + 1 AS Dim7,
Floor(Rand() * 10) + 1 AS Dim8,
Floor(Rand() * 10) + 1 AS Dim9,
Floor(Rand() * 10) + 1 AS Dim10,
Floor(Rand() * 100) + 1 AS Value1
AutoGenerate(20000000);

_Table2:
Noconcatenate LOAD
'2023' as Year,
Floor(Rand() * 10) + 1 AS Dim1,
Floor(Rand() * 100) + 1 AS Value2
AutoGenerate(20000000);

_Table3:
Noconcatenate LOAD
'2023' as Year,
Floor(Rand() * 10) + 1 AS Dim1,
Floor(Rand() * 10) + 1 AS Dim2,
Floor(Rand() * 100) + 1 AS Value3
AutoGenerate(20000000);

LinkTable:
LOAD Distinct
Year & '|' & Dim1 & '|' & Dim2 as Table1CompositeKey,
Year & '|' & Dim1 as Table2CompositeKey,
Year & '|' & Dim1 & '|' & Dim2 as Table3CompositeKey,
Year,
Dim1,
Dim2
Resident _Table1;

Concatenate(LinkTable)
LOAD Distinct
Year & '|' & Dim1 & '|' & null() as Table1CompositeKey,
Year & '|' & Dim1 as Table2CompositeKey,
Year & '|' & Dim1 & '|' & null() as Table3CompositeKey,
Year,
Dim1,
null() as Dim2
Resident _Table2;

Concatenate(LinkTable)
LOAD Distinct
Year & '|' & Dim1 & '|' & Dim2 as Table1CompositeKey,
Year & '|' & Dim1 as Table2CompositeKey,
Year & '|' & Dim1 & '|' & Dim2 as Table3CompositeKey,
Year,
Dim1,
Dim2
Resident _Table3;

Table1:
LOAD
Year & '|' & Dim1 & '|' & Dim2 as Table1CompositeKey,
Dim3,
Dim4,
Dim5,
Dim6,
Dim7,
Dim8,
Dim9,
Dim10,
Value1
Resident _Table1;

Drop table _Table1;

Table2:
LOAD
Year & '|' & Dim1 as Table2CompositeKey,
Value2
Resident _Table2;

Drop table _Table2;

Table3:
LOAD
Year & '|' & Dim1 & '|' & Dim2 as Table3CompositeKey,
Value3
Resident _Table3;

Drop table _Table3;

Link table data model:

I have qlik sense business cloud version.
I need to automate a weekly scheduled app to send CSV file attached to email using SMTP Qlik connector.
How can this be achieved and if it is not feasible, please suggest all workarounds.

#Qlik_Sense_Business_Cloud, SMTP_Mail, Qlik_Connectors, #connector

Dear Qlik Team

I have a question regarding the interplay between Change Processing and Filters. Let's consider the following scenario:

We have a replication form Oracle to SQL Server with Change Processing (using Replicate Log Reader) with "Apply Changes".

Here is the state of the source table before replicating:

ColA, ColB, ColC
secret, a , 500
public, b , 600

In the Out-Task, we activate a filter including only rows with ColA = 'public'.

State of the target table after initial full load:

ColA, ColB, ColC
public, b , 600

While in Change Processing, on the source DB the following Statement is committed:

UPDATE ColB SET ColB = 'secretValue' WHREE ColC = 500

Question:

Which of the following Statements is true? I would like to know for transactional mode and also for Batch Apply mode (attrep_changes temp tables sent to target DB).

1. The Qlik Server checks locally if the affected row is filtered or not. It notes that the updated row is not included in the filter expression, therefore the commit is ignored and nothing is sent to the target machine.
2. The Information of the update statement is sent to the target machine, but it will be ignored there.

Best Regards, Chris

Buenas tardes, quisiera tener gráficos y KPI's acumulativos, es decir que si filtro abril, me de el acumulado histórico hasta abril, no sólo los datos que aparecen en abril, ¿hay manera de hacerlo?

I was wondering if anybody has any experience with Qlik Alerts through Qlik Embed.

To give a bit of backstory:

We are designing a suite of Qlik Sense apps which will be embedded into our internal site.

This is now set up so that there is a landing page which has the available apps as tiles, using their thumbnails which are pulled back through the API.

When the app is selected, then Qlik Embed is hosting the full app.

However, a big part of what we hoped to achieve with this launch is to also use Qlik Alerts. This obviously shows up through the bell on the Qlik tenant where you can look at your alerts.

Has anybody any ideas or experience with also embedding this into a site?

I have an automation that retrieves a list of data from an app, which I want to save in a "temporary" table in Qlik in order to update a column in a table within the same app. In other words, I select a list of contacts, then through a button, I trigger an automation that updates the status of those contacts to "processed".

Is it possible to create this table using Qlik Sense Automation to allow me to update the status of these contacts?

Do you know which blocks I can use?

Thanks 🙂