topic Re: Left join question in QlikView

Left join question

Fri, 16 May 2014 08:20:58 GMT

File_1 content:

Channel_Category	Order_category
DR
RT
WH
WH	PROJ
WH	SPOR
WH	SPPJ

File_2 content:

Channel_string	Channel
DR\|\|	DE
RT\|\|	RE
WH\|\|	TR
WH\|\|PROJ	PRJ
WH\|\|SPOR	TR
WH\|\|SPPJ	PRJ

Table_A:

Load Channel_Category,

Order_category,

Channel_Category&'||'&Order_Category as Channel_String

From File_1;

Left Join(Table_A)

Load Channel_String,

Channel

From File_2;

Then I display the table box of Table A is as below

Channel_Category	Order_category	Channel_String	Channel
DR		DR\|\|	-
RT		RT\|\|	-
WH		WH\|\|	-
WH	PROJ	WH\|\|PROJ	PRJ
WH	SPOR	WH\|\|SPOR	TR
WH	SPPJ	WH\|\|SPPJ	PRJ

Look at the table box, seems like the 'left join' for the first 3 records fails returns a valid value (yellow highlighted). Is this due to a 'blank' value in the load '&' statement? Or I am guessing that there is a invisible character in File_1 which causes the the failure, because there is no invisible characters in File_2 key field.

I am expecting the first 3 entry on the field 'Channel' returns 'DR', 'RE', 'TR'

Anyone shares light? Thanks.

Jack

Re: Left join question

its_anandrjs — Fri, 16 May 2014 08:36:14 GMT

Try with this load script

Table_A:

LOAD *, Channel_Category&'||'&Order_Category as Channel_String;

LOAD * INLINE [

Channel_Category, Order_Category

DR,

RT,

WH,

WH, PROJ

WH, SPOR

WH, SPPJ];

Left Join

LOAD * INLINE [

Channel_String, Channel

DR|| ,DE

RT|| ,RE

WH|| ,TR

WH||PROJ, PRJ

WH||SPOR, TR

WH||SPPJ, PRJ];

Re: Left join question

Fri, 16 May 2014 09:09:06 GMT

Thanks Anand, but it does not work.

Re: Left join question

jagan — Fri, 16 May 2014 09:13:12 GMT

Hi,

Try Trim()

Table_A:

LOAD *, Channel_Category&'||'& Trim(Order_Category) as Channel_String;

LOAD * INLINE [

Channel_Category, Order_Category

DR,

RT,

WH,

WH, PROJ

WH, SPOR

WH, SPPJ];

Left Join

LOAD * INLINE [

Channel_String, Channel

DR|| ,DE

RT|| ,RE

WH|| ,TR

WH||PROJ, PRJ

WH||SPOR, TR

WH||SPPJ, PRJ];

Regards,

Jagan.

Re: Left join question

crusader_ — Fri, 16 May 2014 09:14:42 GMT

Hi, Jack

Firstly, check wether is a missprint:

Left Join(Table_A)

Load Channel_String,

File_2 content:

Category_string	Channel

Also check whether is Order_Category in first source is not NULL, but just empty string.

Hope this helps.

Andrei

Re: Left join question

jonathandienst — Fri, 16 May 2014 09:18:00 GMT

I tested that script and it works for me (Channel is populated for all rows)

Regards

Jonathan

Re: Left join question

Fri, 16 May 2014 09:20:34 GMT

Hi Andrei, this was a type error. I was attempting 'Channel_string'.

This is also what i am doubting: the field Order_Category is not null but with invisible characters...

The first file is a QVD file, i am not able to confirm that if it is NULL;

The second file is a flat file i create locally.

Jack

Re: Left join question

Fri, 16 May 2014 09:26:03 GMT

Hi Jagan,

Trim() works, thanks a lot! So do you think it is an issue with invisible characters?

Jack

Re: Left join question

crusader_ — Fri, 16 May 2014 09:27:44 GMT

Hi, Jack

You can easy check whether the string empty or not if you create additional coloumn len(Order_category).

Empty string should return 0. To delete spaces use ltrim() , rtrim() - functions or purgechar().

Hope this helps.

Andrei

Re: Left join question

jagan — Fri, 16 May 2014 10:06:05 GMT

May be some blank spaces in the field. Please close this thread.

Regards,

jagan.

Re: Left join question

Roop — Fri, 16 May 2014 10:33:26 GMT

use where LEN(TRIM(Order_Category)) <> 0

at the end of the load and that should get rid of the blanks.

The TRIM statement should remove leading and trailing blanks and then leave a length of zero.

Re: Left join question

its_anandrjs — Fri, 16 May 2014 11:16:29 GMT

I not understand clearly i get the below mention output from the script that i reply let me know.

See the output