Mapping of fields based on specific language

[berryandcherry6]

HI,

i have a script like below

INVITATION:

LOAD * INLINE [

    unique_id, language_id,batch_id

1234,1,23

1235,2,23

1236,2,24

1237,1,25

1267,2,26

];

USERQUESTION:

LOAD * INLINE [

question_id, answer_choice_id as answerID,unique_id

23,3,1234

24,4,1235

23,3,1237

];

ANSWERTEXT:

Load * INLINE[

answer_id as answerID, language_id, answer_text

3,1,forgot to pay

3,2,se olvidó de pagar

4,1,short on cash

4,2,corto en efectivo

];

where language_id = 1 is english and lnaguage = 2 is spanish.

when i run this script, i get below output

But i need like below, where answer text language depends on unique_id language.

unique_id	question_id	answer_text
1234	23	forgot to pay
1235	24	corto en efectivo
1237	23	forgot to pay

How could i do this?

[sunny_talwar]

May be join the tables together?

INVITATION:

LOAD * INLINE [

    unique_id, language_id, batch_id

    1234, 1, 23

    1235, 2, 23

    1236, 2, 24

    1237, 1, 25

    1267, 2, 26

];

Left Join (INVITATION)

LOAD * INLINE [

    question_id, answerID, unique_id

    23, 3, 1234

    24, 4, 1235

    23, 3, 1237

];

Left Join (INVITATION)

LOAD * INLINE [

    answerID, language_id, answer_text

    3, 1, forgot to pay

    3, 2, se olvidó de pagar

    4, 1, short on cash

    4, 2, corto en efectivo

];

[big_dreams]

how was your data model look like???

Regards,

[berryandcherry6]

Hi sunny,

joining is fine, i had this thought in my mind.

Is there other option we can handle this in front end. So it could show answer text of language type as same as unique id language?

Thanks,

Supriya

[sunny_talwar]

I mean do you not have answerID connected to each other to avoid loops?

[berryandcherry6]

Hi sunny,

i didn't get your question! but i try to answer what i have understood.

userquestion and answertext table are connected through answerID field, and this answerID field can be found in only these two tables.

is this answer you excepted from me?

[sunny_talwar]

So there is no connection based on language_id in INVITATION and ANSWERTEXT?

[berryandcherry6]

yes, no connection between those tables based on languageid

[sunny_talwar · Accepted solution]

So, I guess you can use an if statement like this in your expression

If(language_id4mINVITATION = language_id4mANSWERTEXT, .....)

[berryandcherry6]

Hi sunny,

that works, if i use inside if condition.

What if i am using in setAnalysis,

i tried using below expressions  doesnot work

only({$<question_id={'315','311'} , invitation_language_id = {'answertext_language_id'}>}answer_text) or

only({$<question_id={'315','311'} , invitation_language_id = 'answertext_language_id'>}answer_text)

Thanks