topic Rows processing from select in QlikView

Rows processing from select

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

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?

Re: Rows processing from select

vishsaggi — Wed, 24 May 2017 17:00:17 GMT

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.

Re: Rows processing from select

Digvijay_Singh — Wed, 24 May 2017 17:13:48 GMT

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.

Re: Rows processing from select

sunny_talwar — Wed, 24 May 2017 17:17:05 GMT

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;

Re: Rows processing from select

Anonymous — Wed, 24 May 2017 19:05:14 GMT

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

Re: Rows processing from select

sunny_talwar — Wed, 24 May 2017 19:55:52 GMT

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;

Re: Rows processing from select

Anonymous — Wed, 24 May 2017 20:02:38 GMT

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