topic Re: count the records in two tables association in App Development

count the records in two tables association

Anonymous — Fri, 29 Dec 2017 10:26:35 GMT

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

Re: count the records in two tables association

mdmukramali — Fri, 29 Dec 2017 17:40:21 GMT

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.

Re: count the records in two tables association

Anonymous — Fri, 29 Dec 2017 18:06:45 GMT

Maybe something like the attached.

Re: count the records in two tables association

mdmukramali — Fri, 29 Dec 2017 18:32:30 GMT

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,

Re: count the records in two tables association

Anonymous — Tue, 02 Jan 2018 14:38:31 GMT

@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?

Re: count the records in two tables association

Anonymous — Tue, 02 Jan 2018 14:40:21 GMT

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!

Re: count the records in two tables association

Anonymous — Tue, 02 Jan 2018 17:22:19 GMT

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.

Re: count the records in two tables association

Anonymous — Fri, 05 Jan 2018 18:59:12 GMT

Great, thank you!

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