topic Re: IF IS NULL SELECT FIELD FROM concatenated join table in QlikView

IF IS NULL SELECT FIELD FROM concatenated join table

matthewp — Mon, 21 Nov 2016 09:16:05 GMT

Ok so i have a block of data that is built from 3 qvds, it is basically generated by..

TAB1:

LOAD * FROM qvd1

LEFT JOIN (TAB1)

TAB2:

LOAD * FROM qvd2

Resulttable:

NoConcatenate

LOAD *

RESIDENT TAB1

DROP TABLE TAB1

Finaltable:

Concatenate (Resulttable)

LOAD * FROM qvd3;

Now here is a snippet of the data:

ID	REF	NAME	Status	Date	INo	IAmt	IType
856	ABC 112	Matthew	Good	21/11/2016	1102	500	P
856	ABC 112	Matthew	Good	21/11/2016	1157	220	P
856	ABC 112	Matthew	Good	21/11/2016	1184	450	P
856	-	-	-	-	1178	600	I
856	-	-	-	-	1127	800	I
856	-	-	-	-	1199	750	I

And here is what i want:

ID	REF	NAME	Status	Date	INo	IAmt	IType
856	ABC 112	Matthew	Good	21/11/2016	1102	500	P
856	ABC 112	Matthew	Good	21/11/2016	1157	220	P
856	ABC 112	Matthew	Good	21/11/2016	1184	450	P
856	ABC 112	Matthew	Good	21/11/2016	1178	600	I
856	ABC 112	Matthew	Good	21/11/2016	1127	800	I
856	ABC 112	Matthew	Good	21/11/2016	1199	750	I

The vales in RED are from the left join of qvd 1 & 2 (resulttable) the values in BLUE are from qvd3.

They have the ID in common so i want something like IF(ISNULL(NAME),resulttable.name,qvd3.name)

Re: IF IS NULL SELECT FIELD FROM concatenated join table

kuczynska — Mon, 21 Nov 2016 10:47:25 GMT

Hi Matthew,

You can always transform your qvd3 before Concatenating to Resulttable. If you know that ID is common for both qvd3 and qvd2 + qvd 1, why not to try to left join / apply map missing information from qvd 1 & qvd 2 to qvd3 and then concatenate to Resulttable?

This is course only valid if REF, NAME, Status and Date are the exact values that should be mapped to al ID's coming from qvd3.

Hope this makes some sense.

Good luck!

Re: IF IS NULL SELECT FIELD FROM concatenated join table

matthewp — Mon, 21 Nov 2016 11:03:04 GMT

Ive used apply maps before but how would i go about doing it (just for the NAME and i can figure the rest out).

MappingName:

Mapping

LOAD Resulttable.ID,

Resulttable.NAME

FROM

Resulttable:

Then inside the Finaltable have

Finaltable:

Concatenate (Resulttable)

LOAD *,

ApplyMap('MappingName', [ID], NAME) AS [Name]

FROM qvd3;

but cant get it to work

Re: IF IS NULL SELECT FIELD FROM concatenated join table

matthewp — Mon, 21 Nov 2016 11:09:16 GMT

Figured it out...

MAPPING:

MAPPING LOAD [ID],[Name]

RESIDENT resulttable;

Finaltable:

Concatenate (Resulttable)

LOAD *,

ApplyMap('MAPPING', [qvd3.ID], qvd3.NAME) AS [Name]

FROM qvd3;

Re: IF IS NULL SELECT FIELD FROM concatenated join table

kuczynska — Mon, 21 Nov 2016 11:12:43 GMT

Hi,

What values are you getting in [Name] column within Finaltable? Is your code working ok if you would comment out Concatenate(Resulttable) in your load script?

Btw - are you qualifying all fields in Resulttable? If so your code execution may fail on Concatenate(Resultable) step as from QV perspective Resulttable.ID and ID are not the same field and (depending on the volume of data) output table may be too big to handle, etc.

Maybe post your sample app.