Rows processing from select

[Anonymous]

Hello!

I new in Qlik and i need help!  : )

When i make select, can i calculate* additional fields based on fields from table?

calculate* - get additional data from other tables.

Example

Table1
--ID--|--NAME--|--SEX--|--CODE
  1      Pamella    W          555
  2     Tom           M          888

Table2
--CODE--|--Surname
  555         Andersen

Table3
--CODE--|--Surname
  888         Bush

I make select from Table1, and i need get surname for every row from Table2 or Table3 depending on the sex. how can i do this?

[vishsaggi]

What is the issue here? Did you load those three tables. As your key field is CODE in all the three tables. When you load your data Table2 and table3 will autoconcatenate and join with Table1 based on CODE field.

Table1:

LOAD * INLINE [

ID,NAME,SEX,CODE

  1,      Pamella,    W,          555

  2,     Tom,         M,          888

]; 

Table2:

LOAD * INLINE [

CODE,Surname

555,  Andersen

];

Table3:

LOAD * INLINE [

CODE,Surname

888,  Bush

];

Just add a straight table and add all these fields as dimensions and 1 as your expr. You can see something like this.

[Digvijay_Singh]

You have a CODE field common in all the tables, so Qlik will automatically associated Table 1 data with Table2 and Table3, don't need to do anything on association, also you won't have table 3 when you load all 3 tables as Table 2 and Table 3 will be automatically concatenated by virtue of all same field name.

But SEX is not available in Table2 and 3 so can you elaborate further on your requirements.

[sunny_talwar]

Sure you can do this. May be like this:

MappingWomen:

Mapping

LOAD CODE,

    Surname

FROM Table2;

MappingMen:

Mapping

LOAD CODE,

    Surname

FROM Table3;

Fact:

LOAD ID,

    NAME,

    SEX,

    CODE,

   If(SEX = 'W', ApplyMap('MappingWomen', CODE, Null()), ApplyMap('MappingMen', CODE, Null())) as Surname

FROM Table1;

[Anonymous]

Thanks all for replies!

Ok, but if key consists of two fields: "Document Number" + "Doc Type", can i use this method (mapping)? or exist any other method?

Table1
--Document number--|--Doc Type--|--Amount--
            123                  DT1            25.50
            456                  DT2            200.77

Table2

--Document number--|--Doc Type--|--Buyer--

            123                  DT1            Alex

Table3

--Document number--|--Doc Type--|--Buyer--

            456                  DT2            Bill

[sunny_talwar · Accepted solution]

You can by combining the two fields into one using &

MappingWomen:

Mapping

LOAD DocNum&DocType,

    Buyer

FROM Table2;

MappingMen:

Mapping

LOAD DocNum&DocType,

    Buyer

FROM Table3;

Fact:

LOAD DocNum,

    DocType,

    Amount

  If(DocType = 'DT1', ApplyMap('MappingWomen', DocNum&DocType, Null()), ApplyMap('MappingMen', DocNum&DocType, Null())) as Surname

FROM Table1;

[Anonymous]

It is what i need, thank you! i didn't know what could using "combining"!