topic Re: Writing formula. Subtracting Count between 2 tables. in QlikView

Writing formula. Subtracting Count between 2 tables.

Mon, 17 Oct 2011 19:11:17 GMT

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.

Re: Writing formula. Subtracting Count between 2 tables.

its_anandrjs — Mon, 17 Oct 2011 19:26:59 GMT

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

Writing formula. Subtracting Count between 2 tables.

Mon, 17 Oct 2011 19:29:38 GMT

Try this

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.

its_anandrjs — Mon, 17 Oct 2011 19:31:39 GMT

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.

its_anandrjs — Mon, 17 Oct 2011 20:28:54 GMT

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

Writing formula. Subtracting Count between 2 tables.

Mon, 17 Oct 2011 21:04:02 GMT

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.

its_anandrjs — Tue, 18 Oct 2011 03:54:52 GMT

Hi,

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

Rgds

Anand