topic Re: join issue!!!! in QlikView

join issue!!!!

Mon, 09 Jun 2014 09:45:06 GMT

Hi All

can any one help me to to find the id records which are having status value as top and bottom. in front end with an expression.

Re: join issue!!!!

tresB — Mon, 09 Jun 2014 09:47:24 GMT

In a text box try like:

=Concat( Distinct {<Status={'top', 'Bottom'}>} ID, ',')

Re: join issue!!!!

Mon, 09 Jun 2014 10:49:56 GMT

Hi Tresesco i would like to apply this expression on 300,000 records. in a straight table.

Re: join issue!!!!

tresB — Mon, 09 Jun 2014 10:52:32 GMT

What would be your dimension? Could you post a sample qvw?

Re: join issue!!!!

Mon, 09 Jun 2014 11:37:47 GMT

I have two tables A,B

A having

id Status

1 top

2 bottom

3 top

B is having

id Status

1 top

1 bottom

2 bottom

2 top

3 top

now i applied inner join so i got id Astatus Bstatus

1 top top

1 top bottom

2 bottom bottom

2 bottom top

3 top top

now can you show the records the records having id Astatus Bstatus

1 top bottom

2 bottom top

in straight table.

Thank you.

Re: Re: join issue!!!!

tresB — Mon, 09 Jun 2014 11:45:53 GMT

PFA

Re: join issue!!!!

anbu1984 — Mon, 09 Jun 2014 11:47:32 GMT

In Script

TabA:

Load * Inline [

id,AStatus

1,top

2,bottom

3,top ];

Inner Join(TabA)

TabB:

Load * Inline [

id,BStatus

1,top

1,bottom

2,bottom

2,top

3,top ];

Final:

NoConcatenate

Load * Resident TabA Where AStatus <> BStatus;

Drop table TabA;

In Front end

Re: join issue!!!!

Mon, 09 Jun 2014 12:30:17 GMT

Thank you.

Re: join issue!!!!

its_anandrjs — Mon, 09 Jun 2014 13:08:59 GMT

In your load script you can map the table1 also for table2 see the load script for that

Table1:

Mapping

Load * Inline [

id,AStatus

1,top

2,bottom

3,top ];

Table2:

LOAD if(ApplyMap('Table1',id) <> BStatus,ApplyMap('Table1',id,'unknown'),'EqualString') as AStatus,BStatus,id;

Load * Inline [

id,BStatus

1,top

1,bottom

2,bottom

2,top

3,top

];

And then in the Straight table take

Dimension1:- AStatus

Dimension2:- BStatus

Expression:- Sum({<AStatus = {'bottom','top'}>} id)