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]

Unfortunately, with this kind of data model, set analysis won't work.... may be you can spend time in creating a link table to avoid loops and then you won't even have to use set analysis or if statement....