Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

count the records in two tables association

I load into my app two tables which have one common field that joins them together - simply like that:

d1:

LOAD

ID,

    d1.param1,

    d1.param2,

    d1.param3

FROM [lib://qvd data/d1.qvd]

(qvd);

d2:

LOAD

ID,

    d2.param_a,

    d2.param_b

FROM [lib://qvd data/d2.qvd]

(qvd);

Data in d1 contains the data with one row per each ID only, whereas d2 can have more rows with the same ID. The data from d1 and d2 is partly common, however there are some records in d1 which do not exist in d2 and the other way round. What I need is to calculate:

1) how many records (distinct) are in d1 and d2

2) how many records (distinct) are in d1 and not in d2

3) how many records (distinct) are in d2 and not in d1


I tried to add new field to each table with value = 1 ('1' as ' d1_bin', '1' as 'd2_bin') and I created a dimension which is based on concatenation of 1 or NULLs:


=if(d1_bin = 1 and d2_bin = 1, 'd1&d2',

if(d1_bin = 1 and IsNull(d2_bin ), 'd1 only',

    if(d2_bin = 1 and IsNull(d1_bin ), 'd2 only')))

which gives the correct result I guess - looks like in the table it puts correct values according to the data. But if I want to select eg. 'd1 only', the selection that appears in the top bar includes d1=1, which actually gives both 'd1 only' and 'd1&d2'.


Is there a way to fix it or do I miss an obvious solution for this? I've searched through the forum but couldn't really use the solutions posted..


Another thing is that I cannot count distinct values of the tables - even if I put count(distinct ID), I am getting the same value as in count(ID) - and it's in fact the result of distinct count - no idea why and no idea how to check what could be the cause...?


Thanks

Kasia

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Maybe something like the attached.

View solution in original post

7 Replies
mdmukramali
Specialist III
Specialist III

Hi,

I have some solutions in my mind to implement.

but can you prepare some dummy data and attach i will work on it .

Thanks,

Mukram.

Anonymous
Not applicable
Author

Maybe something like the attached.

mdmukramali
Specialist III
Specialist III

d1:

LOAD

ID,

    d1.param1,

    d1.param2,

    d1.param3,

     1 as d1_Flag

FROM [lib://qvd data/d1.qvd]

(qvd);

     d2:

     LOAD

     ID,

    d2.param_a,

    d2.param_b,

     1 as d2_Flag,

     'Yes' as Flag

FROM [lib://qvd data/d2.qvd]

(qvd)

Where Exists(ID);


     d2:

     LOAD

     ID,

    d2.param_a,

    d2.param_b,

     1 as d2_Flag,

     'No' as Flag

FROM [lib://qvd data/d2.qvd]

(qvd)

Where NOT Exists(ID);



// Resident Load

NoConcatenate

Load *,

if(d1_Flag=1 and  ISNULL(d2_Flag),'Only d1',

   if(Flag='Yes','d1 & d2', 

     if(Flag='No','d2'))) as Table_Value

Resident d1;

drop table d1;


to count only from d1 table


count(Distinct {<Table_Value={'Only d1'}>}ID)


for in Both:

count(Distinct {<Table_Value={'d1 & d2'}>}ID)


for only d2:

count(Distinct {<Table_Value={'d2'}>}ID)



Thanks,

Mukram,

Anonymous
Not applicable
Author

@Bill,

Thank you for the solution! I transformed it a little bit so that both tables are loaded from .qvd and then joined afterwards. It works! however the app is adding some synthetic keys while loading data... is that ok?

Also, I'm wondering whether there is a chance to combine it into one field saying 'd1 only', 'd2 only', 'd1&d2' but that's just about to compress three filter panes into one, for now it's fine.

What's interesting is that when selected 'd1&d2' to 'Yes' (so I get data existing in both tables), count and count distinct become to behave properly - does it mean that generally I cannot use regular count when having some divergent data?

Anonymous
Not applicable
Author

Thank you @Mohammed! The solution looks good for me, however I am wondering whether it would took twice as much time to perform - it needs to load the tables twice, once looking for existing data in another table, second looking for blanks - but I may be wrong 🙂 Anyway thanks a lot!

Anonymous
Not applicable
Author

If you add the bit in red italics below to my sample qvf then the new field [d Type]  should allow you to just use that for your single filter pane.

Data:

NoConcatenate

Load

  if ( [d1.Table] = 'd1' and isnull([d2.Table])

  , dual('Yes',1) , dual('No',0) )  as [d1.Only] ,

  if ( [d2.Table] = 'd2' and isnull([d1.Table])

  , dual('Yes',1) , dual('No',0) )  as [d2.Only] ,

  if ( [d2.Table] = 'd2' and [d1.Table] = 'd1'

  , dual('Yes',1) , dual('No',0) )  as [d1.and.d2] ,

     

  if ( [d1.Table] = 'd1' and isnull([d2.Table])

  , 'd1.Only' ,

  if ( [d2.Table] = 'd2' and isnull([d1.Table])

  , 'd2.Only' ,

  if ( [d2.Table] = 'd2' and [d1.Table] = 'd1'

  , 'd1.and.d2' ) ) ) as [d Type] ,

  *

resident Temp ;


In my sample I only had one table, but if you have a synthetic key you must have one or more tables.  Double check that any temporary tables are deleted, in my example it does a Drop Table Temp ; at the end.

Anonymous
Not applicable
Author

Great, thank you!

Sure, I did forgot to drop a temp table... thanks 🙂