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: 
Not applicable

Writing formula. Subtracting Count between 2 tables.

Hi,

I have 2 tables and they have the same values as follows:

Table1:

Field, Count1

A,1

B,2

C,3

D,4

Table2:

Field, Count2

A,5

B,3

C,8

D,9

i want to generate a 3rd table like:

Field, Count1-Count2

A, -4

B,-1.....

The value of Field is same. Table 3 should take the counts and subtract it based the values A, B, C,D...

thanks and i will appreciate your responses.

1 Solution

Accepted Solutions
its_anandrjs

Hi,

I suggest you to load this two tables and make a join in between them and then after loading calculate the Count1-Count2 on fields then you can achive this

Some thing like below

Table1:

load * inline

[

Field, Count1

A,1

B,2

C,3

D,4

];

Table2:

Join

load * inline

[

Field, Count2

A,5

B,3

C,8

D,9

];

See the attached sample file.

Rgds

Anand

View solution in original post

6 Replies
its_anandrjs

Hi,

I suggest you to load this two tables and make a join in between them and then after loading calculate the Count1-Count2 on fields then you can achive this

Some thing like below

Table1:

load * inline

[

Field, Count1

A,1

B,2

C,3

D,4

];

Table2:

Join

load * inline

[

Field, Count2

A,5

B,3

C,8

D,9

];

See the attached sample file.

Rgds

Anand

Not applicable
Author

Try this

JJ

Table1:

Load * Inline [

Field, Count1

A,1

B,2

C,3

D,4] ;

Left join(Table1)

Load *   Inline [

Field, Count2

A,5

B,3

C,8

D,9];

Load * , Count1-Count2 as Diff

Resident Table1 ;

its_anandrjs

Hi,

In load also you can do this like

Table1:

load * inline

[

Field, Count1

A,1

B,2

C,3

D,4

];

Table2:

Join

load * inline

[

Field, Count2

A,5

B,3

C,8

D,9

];

Data:

load

Field,

Count1-Count2 as DiffCount

Resident Table1;

Drop table Table1;

See attached

Rgds

Anand

its_anandrjs

Hi,

Hope you got correct answer from this if so mark this post as correct or help full so other can see it.

Rgds

Anand

Not applicable
Author

Hi Anand,

Thanks for the soultion above. I Have another case where the Field is not same, as in the name is same but in table1 and table 2 names are like ABC, abc. The problem with the uppercase and lowercase. How can we resolve this?

Thank you.

its_anandrjs

Hi,

Thanks You can rename the field like Rename field abc to ABC.

Rgds

Anand