Left join resident tables

[#1]

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?

[#2]

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

Is that the reason? Cant this be avoided?

[#3]

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

[#5]

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

[#7]

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

[#8]

ITs basically the same - IT cannot find TempActiceCookie.

[its_anandrjs · Accepted solution]

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;

[#10]

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