topic Re: Left join resident tables in QlikView

Left join resident tables

Tue, 13 May 2014 06:52:28 GMT

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?

Re: Left join resident tables

Tue, 13 May 2014 07:01:37 GMT

I get this error message when trying to Left join

Illegal combination of prefixes

NoConcatenate

Left join(Temp1)

Sales:

Load

Re: Left join resident tables

amit_saini — Tue, 13 May 2014 07:01:43 GMT

Hi Thomas,

Try this:

Temp1:

Load distinct Names,Numbers

resident Oldtable;

NoConcatenate

Left Join(Temp1)

Load

IDs,Products,Names

Resident OldTable

Thanks,

Re: Left join resident tables

alexandros17 — Tue, 13 May 2014 07:02:13 GMT

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!

Re: Left join resident tables

anbu1984 — Tue, 13 May 2014 07:04:15 GMT

What is the expected output?

Why do you want to use NoConcatenate with Left join?

Re: Left join resident tables

Tue, 13 May 2014 07:08:01 GMT

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

Re: Left join resident tables

alexandros17 — Tue, 13 May 2014 07:12:36 GMT

So before loading the third table use Noconcatenate

MyThird

Noconcatenate

Load * from myThirdTable;

Re: Left join resident tables

anbu1984 — Tue, 13 May 2014 07:16:06 GMT

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

Re: Left join resident tables

Tue, 13 May 2014 08:09:32 GMT

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;

Re: Left join resident tables

Tue, 13 May 2014 08:17:44 GMT

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

Re: Left join resident tables

Tue, 13 May 2014 08:59:15 GMT

not working

Re: Left join resident tables

SunilChauhan — Tue, 13 May 2014 09:16:50 GMT

use left join only not noconcatenate

hope this helps

Re: Left join resident tables

its_anandrjs — Tue, 13 May 2014 09:29:10 GMT

You can use Left Keep in place of the Left Join

Left keep (TempActiveUsersCookies)

Re: Left join resident tables

Tue, 13 May 2014 09:29:10 GMT

When i only use left join it says it can find column Number

Re: Left join resident tables

SunilChauhan — Tue, 13 May 2014 09:31:55 GMT

it means some column Number has some syntax error .may be you can use like [Column Number]

check for that field

hope this helps

Re: Left join resident tables

Tue, 13 May 2014 09:31:58 GMT

like:

TempActiveUsersCookies:

Load Distinct

CookieIDINT as TempActiveUsersCookie,Count(DISTINCT SessionIDINT) as NumberOfBuys

Resident Hej

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

Group by CookieIDINT;

Left Keep (TempActiveUsersCookies)

NoConcatenate

Sales:

Load

CookieIDINT, NumberOfBuys

or?

Re: Left join resident tables

SunilChauhan — Tue, 13 May 2014 09:33:30 GMT

it means some column Number has some syntax error .may be you can use like [Column Number]

check for that field

hope this helps

Re: Left join resident tables

its_anandrjs — Tue, 13 May 2014 09:36:58 GMT

Try with

Left keep (TempActiveUsersCookies)

and not Noconcatenate

Re: Left join resident tables

Tue, 13 May 2014 09:38:45 GMT

If i do like that with no Noconcatenate it says filed not found (NumberOfBuys)

Re: Left join resident tables

its_anandrjs — Tue, 13 May 2014 09:48:53 GMT

Check your code may be i believe you load any table twice or in the table when you Left Keep the table check the keys and make a composite keys to join them into previous table.

Hope this helps