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: 
Not applicable

Left join resident tables

Hi

I have this two tables

Temp1

Load distinct Names,Numbers

resident Oldtable

NoConcatenate

Sales:

Load

IDs,Products,Names

Resident OldTable

How do i Left join my Temp1 table to Sales when i have a NoConcatenate? Where should my left join be?

31 Replies
Not applicable
Author

The table im trying to do the left join on is resident loaded from another table.

Is that the reason? Cant this be avoided?

Not applicable
Author

Just so other can see what i want:

I have this table:

T1:

LOAD EntryID,

     CreateDate,

     DATE#(DATE(CreateDate,'YYYY-MM-DD')) as Date_Clean,

     num(DATE#(DATE(CreateDate,'YYYY-MM-DD'))) as Date_CleanNumeric,

     Upper(CustomerID) as CustomerID,

     RaptorCustomerKey,

     CookieIDINT,

     SessionIDINT,

     ItemFeatureINT,

     SessionUserFeatureINT,

     UserFeatureINT,

     ServerCookieIDINT,

     EventType,

     ItemID,

     ApiMethod,

     CustomerGUID,

     ItemName,

     Price

FROM

D:\QlikviewLoad\QVD\IncrementalLoad.qvd

(qvd);

I then make a Temptable of this

TempActiveUsersCookies:

Load Distinct

CookieIDINT as TempActiveUsersCookie,Count(DISTINCT SessionIDINT) as NumberOfBuys

Resident T1

Where  upper(EventType) = 'BUY' and not isnull(CookieIDINT)

Group by CookieIDINT;

This temptable i want to join onto this table - Where i want the Field NumberOfBuys to be in my Sales Table.

How do i do this? The bottom table is a NoConcatenated Table because it referes to T1

NoConcatenate

Sales:

Load

EntryID,

    Upper(CustomerID) as CustomerID,

    DATE#(DATE(CreateDate,'YYYY-MM-DD')) as Date_Clean, //Create DateKey to TimeDimension

    num(DATE#(DATE(CreateDate,'YYYY-MM-DD'))) as Date_CleanNumeric,

    Time(Floor(Frac(CreateDate),1/24/60),'hh:mm') as Time,

    Time(Floor(Frac(CreateDate),1/24/60),'hh:mm') as Time2, //Create HourKey To TimeDimension

    CreateDate,

    SessionIDINT,

    CookieIDINT,

    ItemName,

    Price,ItemID,CustomerGUID,ApiMethod,

      Resident T1

    Order by CreateDate;

its_anandrjs

You can try with this

Left Join(Sales)

TempActiveUsersCookies:

Load Distinct

CookieIDINT as TempActiveUsersCookie,Count(DISTINCT SessionIDINT) as NumberOfBuys

Resident T1

Where  upper(EventType) = 'BUY' and not isnull(CookieIDINT)

Group by CookieIDINT;

And in the sales table add one line

NoConcatenate

Sales:

Load

EntryID,

CookieIDINT,

CookieIDINT as TempActiveUsersCookie

..

And rest of the codes

Not applicable
Author

Not understood - my temptable comes before my Sales table. I cant load sales tables twice because it consists of millions of rows.

its_anandrjs

Update now check the script

Hi Thomas if you load your table like below script and not create the resident table Sales because it contains only two fields different but you can create this fields in the T1 table also.

T1:

LOAD EntryID,

     CreateDate,

     DATE#(DATE(CreateDate,'YYYY-MM-DD')) as Date_Clean,

     num(DATE#(DATE(CreateDate,'YYYY-MM-DD'))) as Date_CleanNumeric,

     Time(Floor(Frac(CreateDate),1/24/60),'hh:mm') as Time,  // Create this field in the T1 table

     Time(Floor(Frac(CreateDate),1/24/60),'hh:mm') as Time2, //Create HourKey To TimeDimension  Create this field in the T1 table

     Upper(CustomerID) as CustomerID,

     RaptorCustomerKey,

     CookieIDINT,

     SessionIDINT,

     ItemFeatureINT,

     SessionUserFeatureINT,

     UserFeatureINT,

     ServerCookieIDINT,

     EventType,

     ItemID,

     ApiMethod,

     CustomerGUID,

     ItemName,

     Price

FROM

D:\QlikviewLoad\QVD\IncrementalLoad.qvd

(qvd);

Left Join


TempActiveUsersCookies:

Load Distinct

CookieIDINT as TempActiveUsersCookie,Count(DISTINCT SessionIDINT) as NumberOfBuys

Resident T1

Where  upper(EventType) = 'BUY' and not isnull(CookieIDINT)

Group by CookieIDINT;

Sales://Which is final table load this table from the T1

LOAD EntryID,

          CreateDate,

          Date_Clean,

          Date_CleanNumeric,

          Time,

          Time2, //Create HourKey To TimeDimension

          CustomerID,

          RaptorCustomerKey,

          CookieIDINT,

          SessionIDINT,

          ItemFeatureINT,

          SessionUserFeatureINT,

          UserFeatureINT,

          ServerCookieIDINT,

          EventType,

          ItemID,

          ApiMethod,

          CustomerGUID,

          ItemName,

          Price,

          TempActiveUsersCookie,

          NumberOfBuys

Resident T1

Order by CreateDate;

Drop Table T1;

Hope this helps

Not applicable
Author

What about all the flags i have in my Sales table? If you look at the whole script i have a lot of temp tables which i use to create flags in my Sales table

Not applicable
Author

ITs basically the same - IT cannot find TempActiceCookie.

its_anandrjs

Ok One of the other way is if you put TempActiveUsersCookies below Sales table and left join on the base of the TempActiveUsersCookie

Noconcatenate

Sales:

Load

CookieIDINT,

CookieIDINT as TempActiveUsersCookie,

Fields,

Rest of the fields

Resident T1;


Left Join


TempActiveUsersCookies:

Load Distinct

CookieIDINT as TempActiveUsersCookie,Count(DISTINCT SessionIDINT) as NumberOfBuys

Resident T1

Where  upper(EventType) = 'BUY' and not isnull(CookieIDINT)

Group by CookieIDINT;

Not applicable
Author

I need it before, because i need to make a flag which says if the number is above something then write some text.

its_anandrjs

Finally i suggest you have to use qvds in this case like when doing some transformation in the table load then try to store them and then reload for the join logic may be this will help you because some time it is hard to create the fact tables.

Hope this helps