straight table shows not related records

[berryandcherry6]

Hi,

INVITATION:

LOAD * INLINE [

unique_id,batch_id

2334,2

3223,2

2334,3

];

SITTINGINFO:

LOAD * INLINE [

unique_id,sitting_info_id,sitting_id,batch_id as sitting_batch_id

2334,11,3,2

2334,1,1,2

2334,11,2,3

2334,1,3,3

];

My required output should be

Unique ID	batch ID	sitting ID
2334	2	3
2334	3	2

because in SITTINGINFO Table, for unique_id =2334, there are two types. i.e sitting_id are 3 and 2 where unique_id lies in both batch.

How could i do this?

[MK_QSL · Accepted solution]

use something like this

INVITATION:

LOAD *, unique_id & batch_id as Key INLINE 
[
 unique_id,batch_id
 2334,2
 3223,2
 2334,3
];



SITTINGINFO:
LOAD 
// unique_id,
 sitting_info_id,
 sitting_id,
 batch_id as sitting_batch_id,
 unique_id&batch_id as Key INLINE 
[
 unique_id,sitting_info_id,sitting_id,batch_id
 2334,11,3,2
 2334,1,1,2
 2334,11,2,3
 2334,1,3,3
];

Now use

unique_id and batch_id as dimension

and

expression

ONLY(IF(sitting_info_id = 11, sitting_id))

[ChennaiahNallani]

try this way

INVITATION:

Load * INLINE [

unique_id,batch_id

2334,2

3223,2

2334,3

];

INVITATION1:

Mapping Load

unique_id,

batch_id

Resident INVITATION;

Drop Table INVITATION;

SITTINGINFO:

LOAD ApplyMap('INVITATION',unique_id) as batch_id,

* INLINE [

unique_id,sitting_info_id,sitting_id,batch_id as sitting_batch_id

2334,11,3,2

2334,1,1,2

2334,11,2,3

2334,1,3,3

];

Expression

=if(sitting_info_id = 11,sitting_id)

[berryandcherry6]

Hi Manish,

I need explaination on why it was not working before as all equations seems alright.

Here is my explaination, for why it should have worked without issues

In my example, i have one unique id which is present in both batches. And that Unique id has two sitting info i.e 11 and 1 for each batch. Then in expression i am telling it to give sitting id where sitting info is only when = 11. So this should have worked by showing two rows for two batches  with respective sitting id's. and Why it was showing duplicate values.

I will be happy to know your answer on this.

Thanks

Supriya