Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

francisco_cohen
New Contributor III

Tables synchronization

Hi,

I have 3 tables, and want to check the synchronization status between them.

[Source 1]:

LOAD

     Project Code,

     'Yes' as Table1

FROM file1.xlsx

[Source 2]:

LOAD

     Project Code,

     'Yes' as Table2

FROM file1.xlsx

[Source 3]:

LOAD

     Project Code,

     'Yes' as Table3

FROM file1.xlsx

My dashboard has the following table:

Project CodeTable1Table2Table3Synchronized
Project 1Yes--No
Project 2-Yes-No
Project 3Yes-YesNo
Project 4YesYesYesYes

I'm not being able to create the Synchronized dimension, it may be basic!

The best approach I've reached is:

Synchronized formula: if(len(Table1)+len(Table2)+len(Table3)=9,'Yes','No').

This works but when I try to filter my table by synchronized=No it oesn't work....

Any idea on how to solve this?

Thank you.

FC.

1 Solution

Accepted Solutions

Re: Tables synchronization

You couldn't select the result of an expression else only dimensions. But you could create a calculated dimension from it - for example the following within a listbox by choosing expression instead of a field:

aggr(if(len(Table1)+len(Table2)+len(Table3)=9,'Yes','No'), [Project Code])

Otherwise you need to create this as a field within the script, maybe with something like this:

t1:

load distinct [Project Code], 1 as A from source1;

     join

load distinct [Project Code], 1 as B from source2;

     join

load distinct [Project Code], 1 as C from source3;

t2:

load [Project Code], if(rangesum(A,B,C) = 3, 'Yes', 'No') as Synchronized resident t1;

Depending on your requirements and your data might a similar approach with mapping more suitable: Mapping as an Alternative to Joining.

- Marcus

1 Reply

Re: Tables synchronization

You couldn't select the result of an expression else only dimensions. But you could create a calculated dimension from it - for example the following within a listbox by choosing expression instead of a field:

aggr(if(len(Table1)+len(Table2)+len(Table3)=9,'Yes','No'), [Project Code])

Otherwise you need to create this as a field within the script, maybe with something like this:

t1:

load distinct [Project Code], 1 as A from source1;

     join

load distinct [Project Code], 1 as B from source2;

     join

load distinct [Project Code], 1 as C from source3;

t2:

load [Project Code], if(rangesum(A,B,C) = 3, 'Yes', 'No') as Synchronized resident t1;

Depending on your requirements and your data might a similar approach with mapping more suitable: Mapping as an Alternative to Joining.

- Marcus