topic Re: Mapping of fields based on specific language in QlikView

Mapping of fields based on specific language

berryandcherry6 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Mapping of fields based on specific language

sunny_talwar — Thu, 08 Mar 2018 12:24:52 GMT

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

];

Re: Mapping of fields based on specific language

big_dreams — Thu, 08 Mar 2018 12:25:52 GMT

how was your data model look like???

Regards,

Re: Mapping of fields based on specific language

berryandcherry6 — Thu, 08 Mar 2018 12:55:13 GMT

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

Re: Mapping of fields based on specific language

sunny_talwar — Thu, 08 Mar 2018 13:00:46 GMT

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

Re: Mapping of fields based on specific language

berryandcherry6 — Thu, 08 Mar 2018 13:11:01 GMT

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?

Re: Mapping of fields based on specific language

sunny_talwar — Thu, 08 Mar 2018 13:13:32 GMT

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

Re: Mapping of fields based on specific language

berryandcherry6 — Thu, 08 Mar 2018 13:15:37 GMT

yes, no connection between those tables based on languageid

Re: Mapping of fields based on specific language

sunny_talwar — Thu, 08 Mar 2018 13:17:24 GMT

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

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

Re: Mapping of fields based on specific language

berryandcherry6 — Fri, 09 Mar 2018 11:45:27 GMT

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

Re: Mapping of fields based on specific language

sunny_talwar — Fri, 09 Mar 2018 11:59:22 GMT

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....