30 Replies Latest reply: May 13, 2014 2:02 PM by Anand Chouhan RSS

    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?

        • Re: Left join resident tables

          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
              anbu cheliyan

              What is the expected output?

              Why do you want to use NoConcatenate with Left join?

                • Re: Left join resident tables

                  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
                      Alessandro Saccone

                      So before loading the third table use Noconcatenate

                       

                      MyThird

                      Noconcatenate

                      Load * from myThirdTable;

                        • Re: Left join resident tables

                          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
                          anbu cheliyan

                          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
                      Amit Saini

                      Hi Thomas,

                       

                      Try this:

                       

                      Temp1:

                       

                      Load distinct Names,Numbers

                       

                       

                       

                      resident Oldtable;

                       

                       

                       

                       

                       

                      NoConcatenate

                       

                      Left Join(Temp1)

                       

                      Load

                       

                      IDs,Products,Names

                       

                       

                       

                      Resident OldTable

                       

                       

                      Thanks,

                      AS

                      • Re: Left join resident tables
                        Alessandro Saccone

                        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

                          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;

                          • Re: Left join resident tables

                            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;

                              • Re: Left join resident tables
                                Anand Chouhan

                                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

                                • Re: Left join resident tables
                                  Anand Chouhan

                                  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