Left join question

[#1]

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

[its_anandrjs]

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];

[#3]

Thanks Anand, but it does not work.

[jagan]

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.

[crusader_]

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

[jonathandienst]

Hi

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

Regards

Jonathan

[#7]

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

[#8]

Hi Jagan,

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

Jack

[crusader_]

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

[jagan]

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

Regards,

jagan.