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?

1 Solution

Accepted Solutions
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;

View solution in original post

31 Replies
Not applicable
Author

I get this error message when trying to Left join

Illegal combination of prefixes

NoConcatenate

Left join(Temp1)

Sales:

Load

amit_saini
Master III
Master III

Hi Thomas,

Try this:

Temp1:

Load distinct Names,Numbers

resident Oldtable;

NoConcatenate

Left Join(Temp1)

Load

IDs,Products,Names

Resident OldTable

Thanks,

AS

alexandros17
Partner - Champion III
Partner - Champion III

Simply:

Temp1:

Load distinct Names,Numbers

resident Oldtable

Left Join

Load

IDs,Products,Names

Resident OldTable

When you left join the second table's name will be lost because the table is "joined" in the first so the rusulet will be Temp1 table

Let me know!

anbu1984
Master III
Master III

What is the expected output?

Why do you want to use NoConcatenate with Left join?

Not applicable
Author

I have another Table where i full load all my data - I dont want the Sales table to be concatenated with this table.

alexandros17
Partner - Champion III
Partner - Champion III

So before loading the third table use Noconcatenate

MyThird

Noconcatenate

Load * from myThirdTable;

anbu1984
Master III
Master III

Then Noconcatenate should be used before joining another table.

Here you do left join without Noconcatenate

Temp1

Load distinct Names,Numbers

resident Oldtable

Left Join(Temp1)

Sales:

Load

IDs,Products,Names

Resident OldTable

Not applicable
Author

IT does not work - here is my whole script:

Hej:

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

////TEMPCLICK

//LOAD distinct

//CookieIDINT as TempCookieClick,

//ItemID as TempProduct,

//CookieIDINT & '_' & ItemID as TempCookieProductClick,

//CookieIDINT & '_' & SessionIDINT & '_' & ItemID as TempSessionCookieProductClick

//Resident Hej

//Where Upper(EventType) = 'ITEMCLICK';

//

////TEMPBUY

//LOAD distinct

//SessionIDINT as TempSessionIDINTBuy,

//ItemID as TempProduct,

//SessionIDINT & '_' & ItemID as TempSessionProductClick

//Resident Hej

//where Upper(EventType) = 'BUY';

/////--------------------Find Sessions with Itemclick---------------------------/////

TempClick:

Load Distinct

SessionIDINT as TempSessionIDINTClic,

ItemID as TempProduct,

SessionIDINT & '_' & ItemID as TempSessionProductClic

Resident Hej

Where upper(EventType) = 'ITEMCLICK';

TempDaysBetweenBuy:

Load Distinct

CookieIDINT as TempCookieIDINTBuys,

Date_Clean as TempDateCleanBuys,

SessionIDINT as TempSessionIDINTBuys,

Upper(CustomerID) as TempCustomerID

Resident Hej

Where Upper(EventType) = 'BUY'

Order by CookieIDINT,Date_Clean;

//

// IntTable:

//LOAD

//RowNo() as ID,

//RowNo() as Int AutoGenerate 100;

FlagDays:

Load TempCustomerID as CustomerID,TempCookieIDINTBuys,'FlagDays' as FlagType,

TempDateCleanBuys,

//if(TempCookieIDINTBuys = Previous(TempCookieIDINTBuys), TempDateCleanBuys - Previous(TempDateCleanBuys),0) as Int,

if(TempCookieIDINTBuys = Previous(TempCookieIDINTBuys), TempDateCleanBuys - Previous(TempDateCleanBuys),0) as FlagDays,

TempSessionIDINTBuys

Resident TempDaysBetweenBuy

Order by TempCookieIDINTBuys,TempDateCleanBuys;

Concatenate (FlagDays)

FlagMaxDayCookie:

Load max(FlagDays) as FlagDays,TempCookieIDINTBuys,'FlagMaxCookie' as FlagType,CustomerID

Resident FlagDays

Where FlagDays > 0

group by TempCookieIDINTBuys,CustomerID

Order by TempCookieIDINTBuys,CustomerID;

Drop Table TempDaysBetweenBuy;

/////--------------------Find Sessions with Buy---------------------------/////

TempBuy:

LOAD Distinct

    SessionIDINT as TempSessionIDINTBuy,

    ItemID as TempProduct,

    SessionIDINT & '_' & ItemID as TempSessionProductBuy

Resident Hej

Where upper(EventType) = 'BUY';

/////-------------------Find Sessions with Basket----------------------------/////

TempBasket:

LOAD Distinct

    SessionIDINT as TempSessionIDINTBasket,

    ItemID as TempProduct,

    SessionIDINT & '_' & ItemID as TempSessionProductBasket

Resident Hej

Where upper(EventType) = 'BASKET';

TempCookie:

LOAD Distinct

    CookieIDINT as TempCookieClic,

    ItemID as TempProduct,

    CookieIDINT & '_' & ItemID as TempCookieProductClic,

    SessionIDINT & '_' & CookieIDINT & '_' & ItemID as TempSessionCookieProductClic

Resident Hej

Where upper(EventType) = 'ITEMCLICK';

///////--------------------Find All UserIDs---------------------------/////

//TempAllUsers:

//Load Distinct

//SessionUserFeatureINT as TempAllUsers

//Resident Hej;

/////--------------------Find ActiveUsers---------------------------/////

TempActiveUsers:

Load Distinct

SessionUserFeatureINT as TempActiveUsers

Resident Hej

Where CreateDate > Today()-30 and upper(EventType) = 'BUY' and not isnull(SessionUserFeatureINT);

/////--------------------Create a list of old users which is not in active users)----------------------//////

TempActiveUsersCookies:

Load Distinct

CookieIDINT as TempActiveUsersCookie,Count(DISTINCT SessionIDINT) as NumberOfBuys

Resident Hej

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

Group by CookieIDINT; //Set date to minimize what activeusers are from CreateDate > Today()-30 and

//Users who have not bought

TempNonActiveUsersCookies:

Load Distinct

CookieIDINT as TempNonActiveUsersCookie

Resident Hej

Where  upper(EventType) <> 'BUY' and not isnull(CookieIDINT);

/////

//TempOldUsers:

//Load Distinct

//SessionUserFeatureINT as TempLostUsers

//Resident Hej

//Where not exists(TempActiveUsers,SessionUserFeatureINT) and upper(EventType) ='BUY' and len(SessionUserFeatureINT) >0;//CreateDate < Today()-30;// and not exists(TempAllUsers,SessionUserID);

Left join (TempActiveUsersCookies)

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,NumberOfBuys,

    SessionUserFeatureINT,

    Capitalize(EventType) as EventType, //Make all events start with a BIG letter first

    if(upper(EventType) = 'ITEMCLICK' AND exists(TempSessionProductBuy, SessionIDINT & '_' & ItemID), 1, 0) as FlagItemBuy,

    if(upper(EventType) = 'BUY' and Exists(TempCookieProductClic,CookieIDINT & '_' & ItemID),1,0) as FlagFlagBuy,

  

  

  

  

    // the item was clicked, whatever the session

     if(upper(EventType) = 'BUY', if(exists(TempCookieProductClic, CookieIDINT & '_' & ItemID), 1, 0 ), 0)    as FlagProductBuyWhateverSession,

   

   

     if(upper(EventType) = 'BASKET', if(exists(TempCookieProductClic,CookieIDINT & '_' & ItemID),1,0),0) as FlagProductBasketWhateverSession,

   

   

   

     if(upper(EventType) = 'VISIT', if(Exists(TempCookieProductClic,CookieIDINT & '_' & ItemID),1,0),0) as FlagProductVisitWhateverSession,

  

     // the item was clicked but in another session

     if(upper(EventType) = 'BUY', if(exists(TempCookieProductClic, CookieIDINT & '_' & ItemID) AND

             NOT exists(TempSessionCookieProductClic, SessionIDINT & '_' & CookieIDINT & '_' & ItemID), 1, 0 ), 0)    as FlagProductBuyAnotherSession,

  

  

     if(upper(EventType) = 'ITEMCLICK' ,if(exists(TempSessionIDINTBuy, SessionIDINT), -1,2 ),  // If an session contains an itemclick and is founded in Table TempBuy (list over buys) - Then -1 flag to Itemclick

             if(upper(EventType) = 'BUY', if(NOT exists(TempSessionIDINTClic, SessionIDINT), 0,if(exists(TempSessionProductClic,SessionIDINT & '_' & ItemID), 1,3 )),0)) as Flag, //If an session contrains an itemclick but is not founded in table Tempbuy - Then 2 flag to ItemClick

              //If an session Contains a Buy and is founded in the Table TempClick (List over clickevents)  Then 1 to all Buys

    if(upper(EventType) = 'BUY' and CreateDate > Today()-30 ,if(exists(TempActiveUsers,SessionUserFeatureINT),1,0),0) as FlagOneUsers,

      if(upper(EventType) = 'BUY'  ,if(exists(TempActiveUsersCookie,CookieIDINT),1,0),0) as FlagOneUsersCookie,

   

   

 

   

   

   

      if(NOT Exists(TempActiveUsersCookie,CookieIDINT),1,0) as FlagNonActiveUsers //Insert date statement to minimize where users are active from and CreateDate > Today()-30

    Resident Hej

    Order by CreateDate;

Not applicable
Author

my script are as follow and its the text highlighted with bold i want to join

Hej:

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

////TEMPCLICK

//LOAD distinct

//CookieIDINT as TempCookieClick,

//ItemID as TempProduct,

//CookieIDINT & '_' & ItemID as TempCookieProductClick,

//CookieIDINT & '_' & SessionIDINT & '_' & ItemID as TempSessionCookieProductClick

//Resident Hej

//Where Upper(EventType) = 'ITEMCLICK';

//

////TEMPBUY

//LOAD distinct

//SessionIDINT as TempSessionIDINTBuy,

//ItemID as TempProduct,

//SessionIDINT & '_' & ItemID as TempSessionProductClick

//Resident Hej

//where Upper(EventType) = 'BUY';

/////--------------------Find Sessions with Itemclick---------------------------/////

TempClick:

Load Distinct

SessionIDINT as TempSessionIDINTClic,

ItemID as TempProduct,

SessionIDINT & '_' & ItemID as TempSessionProductClic

Resident Hej

Where upper(EventType) = 'ITEMCLICK';

TempDaysBetweenBuy:

Load Distinct

CookieIDINT as TempCookieIDINTBuys,

Date_Clean as TempDateCleanBuys,

SessionIDINT as TempSessionIDINTBuys,

Upper(CustomerID) as TempCustomerID

Resident Hej

Where Upper(EventType) = 'BUY'

Order by CookieIDINT,Date_Clean;

//

// IntTable:

//LOAD

//RowNo() as ID,

//RowNo() as Int AutoGenerate 100;

FlagDays:

Load TempCustomerID as CustomerID,TempCookieIDINTBuys,'FlagDays' as FlagType,

TempDateCleanBuys,

//if(TempCookieIDINTBuys = Previous(TempCookieIDINTBuys), TempDateCleanBuys - Previous(TempDateCleanBuys),0) as Int,

if(TempCookieIDINTBuys = Previous(TempCookieIDINTBuys), TempDateCleanBuys - Previous(TempDateCleanBuys),0) as FlagDays,

TempSessionIDINTBuys

Resident TempDaysBetweenBuy

Order by TempCookieIDINTBuys,TempDateCleanBuys;

Concatenate (FlagDays)

FlagMaxDayCookie:

Load max(FlagDays) as FlagDays,TempCookieIDINTBuys,'FlagMaxCookie' as FlagType,CustomerID

Resident FlagDays

Where FlagDays > 0

group by TempCookieIDINTBuys,CustomerID

Order by TempCookieIDINTBuys,CustomerID;

Drop Table TempDaysBetweenBuy;

/////--------------------Find Sessions with Buy---------------------------/////

TempBuy:

LOAD Distinct

    SessionIDINT as TempSessionIDINTBuy,

    ItemID as TempProduct,

    SessionIDINT & '_' & ItemID as TempSessionProductBuy

Resident Hej

Where upper(EventType) = 'BUY';

/////-------------------Find Sessions with Basket----------------------------/////

TempBasket:

LOAD Distinct

    SessionIDINT as TempSessionIDINTBasket,

    ItemID as TempProduct,

    SessionIDINT & '_' & ItemID as TempSessionProductBasket

Resident Hej

Where upper(EventType) = 'BASKET';

TempCookie:

LOAD Distinct

    CookieIDINT as TempCookieClic,

    ItemID as TempProduct,

    CookieIDINT & '_' & ItemID as TempCookieProductClic,

    SessionIDINT & '_' & CookieIDINT & '_' & ItemID as TempSessionCookieProductClic

Resident Hej

Where upper(EventType) = 'ITEMCLICK';

///////--------------------Find All UserIDs---------------------------/////

//TempAllUsers:

//Load Distinct

//SessionUserFeatureINT as TempAllUsers

//Resident Hej;

/////--------------------Find ActiveUsers---------------------------/////

TempActiveUsers:

Load Distinct

SessionUserFeatureINT as TempActiveUsers

Resident Hej

Where CreateDate > Today()-30 and upper(EventType) = 'BUY' and not isnull(SessionUserFeatureINT);

/////--------------------Create a list of old users which is not in active users)----------------------//////

TempActiveUsersCookies:

Load Distinct

CookieIDINT as TempActiveUsersCookie,Count(DISTINCT SessionIDINT) as NumberOfBuys

Resident Hej

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

Group by CookieIDINT; //Set date to minimize what activeusers are from CreateDate > Today()-30 and

//Users who have not bought

TempNonActiveUsersCookies:

Load Distinct

CookieIDINT as TempNonActiveUsersCookie

Resident Hej

Where  upper(EventType) <> 'BUY' and not isnull(CookieIDINT);

/////

//TempOldUsers:

//Load Distinct

//SessionUserFeatureINT as TempLostUsers

//Resident Hej

//Where not exists(TempActiveUsers,SessionUserFeatureINT) and upper(EventType) ='BUY' and len(SessionUserFeatureINT) >0;//CreateDate < Today()-30;// and not exists(TempAllUsers,SessionUserID);

Left join (TempActiveUsersCookies)

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,NumberOfBuys,

    SessionUserFeatureINT,

    Capitalize(EventType) as EventType, //Make all events start with a BIG letter first

    if(upper(EventType) = 'ITEMCLICK' AND exists(TempSessionProductBuy, SessionIDINT & '_' & ItemID), 1, 0) as FlagItemBuy,

    if(upper(EventType) = 'BUY' and Exists(TempCookieProductClic,CookieIDINT & '_' & ItemID),1,0) as FlagFlagBuy,

 

 

 

 

    // the item was clicked, whatever the session

     if(upper(EventType) = 'BUY', if(exists(TempCookieProductClic, CookieIDINT & '_' & ItemID), 1, 0 ), 0)    as FlagProductBuyWhateverSession,

  

  

     if(upper(EventType) = 'BASKET', if(exists(TempCookieProductClic,CookieIDINT & '_' & ItemID),1,0),0) as FlagProductBasketWhateverSession,

  

  

  

     if(upper(EventType) = 'VISIT', if(Exists(TempCookieProductClic,CookieIDINT & '_' & ItemID),1,0),0) as FlagProductVisitWhateverSession,

 

     // the item was clicked but in another session

     if(upper(EventType) = 'BUY', if(exists(TempCookieProductClic, CookieIDINT & '_' & ItemID) AND

             NOT exists(TempSessionCookieProductClic, SessionIDINT & '_' & CookieIDINT & '_' & ItemID), 1, 0 ), 0)    as FlagProductBuyAnotherSession,

 

 

     if(upper(EventType) = 'ITEMCLICK' ,if(exists(TempSessionIDINTBuy, SessionIDINT), -1,2 ),  // If an session contains an itemclick and is founded in Table TempBuy (list over buys) - Then -1 flag to Itemclick

             if(upper(EventType) = 'BUY', if(NOT exists(TempSessionIDINTClic, SessionIDINT), 0,if(exists(TempSessionProductClic,SessionIDINT & '_' & ItemID), 1,3 )),0)) as Flag, //If an session contrains an itemclick but is not founded in table Tempbuy - Then 2 flag to ItemClick

              //If an session Contains a Buy and is founded in the Table TempClick (List over clickevents)  Then 1 to all Buys

    if(upper(EventType) = 'BUY' and CreateDate > Today()-30 ,if(exists(TempActiveUsers,SessionUserFeatureINT),1,0),0) as FlagOneUsers,

      if(upper(EventType) = 'BUY'  ,if(exists(TempActiveUsersCookie,CookieIDINT),1,0),0) as FlagOneUsersCookie,

  

  

  

  

  

      if(NOT Exists(TempActiveUsersCookie,CookieIDINT),1,0) as FlagNonActiveUsers //Insert date statement to minimize where users are active from and CreateDate > Today()-30

    Resident Hej

    Order by CreateDate;