join issue!!!!

[#1]

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.

[tresesco]

In a text box try like:

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

[#3]

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

[tresesco]

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

[#5]

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.

[tresesco]

PFA

[anbu1984]

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

[#8]

Thank you.

[its_anandrjs · Accepted solution]

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)