Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Left join question

File_1 content:

Channel_CategoryOrder_category
DR
RT
WH
WHPROJ
WHSPOR
WHSPPJ

File_2 content:

Channel_stringChannel
DR||DE
RT||RE
WH||TR
WH||PROJPRJ
WH||SPORTR
WH||SPPJPRJ

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_CategoryOrder_categoryChannel_StringChannel
DRDR||-
RTRT||-
WHWH||-
WHPROJWH||PROJPRJ
WHSPORWH||SPORTR
WHSPPJWH||SPPJPRJ

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

Tags (3)
11 Replies
Highlighted

Re: Left join question

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

Highlighted
Not applicable

Re: Left join question

Thanks Anand, but it does not work.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Left join question

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.

Highlighted
Partner
Partner

Re: Left join question

Hi, Jack

Firstly, check wether is a missprint:

Left Join(Table_A)

Load Channel_String,

File_2 content:

Category_stringChannel

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

Hope this helps.

Andrei

Highlighted
MVP
MVP

Re: Left join question

Hi

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

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Not applicable

Re: Left join question

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

Highlighted
Not applicable

Re: Left join question

Hi Jagan,

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

Jack

Highlighted
Partner
Partner

Re: Left join question

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Left join question

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

Regards,

jagan.