Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joey_lutes
Partner - Creator
Partner - Creator

Join Where not isnull

I have 2 tables.  the requirement is simple - join the data ONLY where the data matches its counterpart.

Table1:

IDITEMColorSize
1Thing1RedS
2Thing2M
3Thing3BlueS

Table2:

IDAltITEMAltColorAltSize
1Thing11PurpleL
2Thing12PinkL
3Thing13BlueXL

Sometimes there won't be a Color in Table 1.  If there isn't, I don't want the Altcolor populated in the FinalTable

FinalTable:

IDITEMColorSizeAltItemAltColorAltSie
1Thing1RedSThing11PurpleL
2Thing2MThing12L
3Thing3BlueSThing13BlueXL

I'm doing something wrong:

Table1:

     Load

          ID, ITEM, Color, Size

    From Source;

Left Join (Table1)

     Load

          ID, AltItem, Altsize

From AltSource;

Left Join (Table1)

     Load

          ID, AltColor

From AltSource

Where not isnull(Color);

Error:  Field 'Color' not found

Help?

Thank you!

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Table1:

Load ID,

     ITEM,

     Color,

     Size

From Source;

Left Join (Table1)

Load ID,

     AltItem,

     Altsize,

     AllColor

From AltSource;

FinalTable:

NoConcatenate

LOAD ID,

     ITEM,

     Color,

     Size,

     AltItem,

     Altsize,

     If(Len(Trim(Color)) > 0, AllColor) as AllColor

Resident Table1;

DROP Table Table1;

View solution in original post

1 Reply
sunny_talwar

May be this:

Table1:

Load ID,

     ITEM,

     Color,

     Size

From Source;

Left Join (Table1)

Load ID,

     AltItem,

     Altsize,

     AllColor

From AltSource;

FinalTable:

NoConcatenate

LOAD ID,

     ITEM,

     Color,

     Size,

     AltItem,

     Altsize,

     If(Len(Trim(Color)) > 0, AllColor) as AllColor

Resident Table1;

DROP Table Table1;