Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Re: Writing formula. Subtracting Count between 2 tables.

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

6 Replies

Re: Writing formula. Subtracting Count between 2 tables.

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

Writing formula. Subtracting Count between 2 tables.

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 ;

Re: Writing formula. Subtracting Count between 2 tables.

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

Re: Writing formula. Subtracting Count between 2 tables.

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

Writing formula. Subtracting Count between 2 tables.

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.

Re: Writing formula. Subtracting Count between 2 tables.

Hi,

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

Rgds

Anand