Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
I get this error message when trying to Left join
Illegal combination of prefixes
NoConcatenate
Left join(Temp1)
Sales:
Load
Hi Thomas,
Try this:
Temp1:
Load distinct Names,Numbers
resident Oldtable;
NoConcatenate
Left Join(Temp1)
Load
IDs,Products,Names
Resident OldTable
Thanks,
AS
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!
What is the expected output?
Why do you want to use NoConcatenate with Left join?
I have another Table where i full load all my data - I dont want the Sales table to be concatenated with this table.
So before loading the third table use Noconcatenate
MyThird
Noconcatenate
Load * from myThirdTable;
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
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;
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;