concatenate and join not working together

[Anonymous]

Hi all,

I am stuck in situation where i am concatenating two tables with same column names but with different data and i am joining it to other table .but join is not happening and it results in to forming multiple sync keys plz help

thnx in advance

[MK_QSL]

Provide your script ... it's difficult to answer without looking into it.

[#3]

Hi Harshal,

I would do it like this.

TABLE:

LOAD *

FROM table1.qvd;

LEFT JOIN (TABLE)

LOAD *

FROM table2.qvd;

CONCATENATE (TABLE)

LOAD *

FROM table3.qvd;

Regards,
Marius

[Anonymous]

[empOnLeave]:

SQL SELECT upper([employee_dasid]) as DASID,

    "full_name",

    "leave_type",

    FromDate,

    ToDate,

    "noof_leaves",

    Action

FROM BETI.dbo."tbl_empOnLeave"

where leave_type not in ('Foreign Deputation');

[ftr]:

Concatenate(empOnLeave)

Load

FTR_DASID as DASID,

FTR_FromDate as FromDate,

Ftr_todate as ToDate,

leave_type;

sql select distinct *,'Foreign deputation' as leave_type from

(

select distinct t.[FTR_DASID],t.FTR_FromDate,t.[Ftr_todate],rank() over(partition by t.[FTR_DASID] order by t.FTR_ToDate) as rnk

from

(

SELECT

       [FTR_ID] AS PK_FTR_ID

      ,[FTR_NO]

      ,[FTR_DASID]

      ,[FTR_DASID] as PK_DASID

   --,B.FIRSTNAME+' '+B.LASTNAME AS FTR_EmployeeName

      ,[FTR_PROJECTCODE]

      ,case when ([FTR_PROJECTCODE]='Non Project') then [FTR_PROJECTCODE] else 'Project' END

  as FTR_PROJECTTYPE

      ,[FTR_PROJECTNAME]

      ,[FTR_DEPT_ID] as PK_ProfitCenter

      ,[FTR_DEPT]

      ,[FTR_COUNTRY_TOVISIT] as PK_COUNTRY_CODE

      ,[FTR_TYPEOFBUSINESS]

      , (SELECT LOOKUP_DESCRIPTION from [FTR].[dbo].[REQ_LOOKUP] where lookup_id = [FTR_TYPEOFBUSINESS] and lookup_active=1) as LKP_TypeOfBusiness

      ,[FTR_PAYROLL_TRANSFER]

      ,[FTR_DURATION]

      ,[FTR_ADVANCE_CURR]

   ,(SELECT LOOKUP_DESCRIPTION from [FTR].[dbo].[REQ_LOOKUP] where lookup_id = [FTR_ADVANCE_CURR] and lookup_active=1) as LKP_CURRENCY

      ,[FTR_FOREX_AMOUNT]

      ,[FTR_ACCO_BY]

   ,(SELECT LOOKUP_DESCRIPTION from [FTR].[dbo].[REQ_LOOKUP] where lookup_id = [FTR_ACCO_BY] and lookup_active=1) as LKP_Accomodation

      ,[FTR_ACCO_BYCOMPANY]

      ,[FTR_STATUS]

   ,(SELECT LOOKUP_DESCRIPTION from [FTR].[dbo].[REQ_LOOKUP] where lookup_id = [FTR_STATUS] and lookup_active=1) as LKP_Status

      ,[FTR_CREATEDDATE]

      ,[FTR_CREATEDBY]

      ,[FTR_APPROVED]

      ,[FTR_SA_REQD]

      ,[FTR_SA_SIGNED]

      ,[FTR_REQ_TYPE]

      ,case when ([FTR_REQ_TYPE]='FTR') then 'Foreign' else 'Local' END

  as FTR_TRAVELTYPE

      ,[SC_PL] as FTR_SC_PL

      ,case when (

             (SELECT MAX(case when [EPNS_BILLABLE_TOCLIENT]=1 then 1 else 0 end) from [FTR].[dbo].[REQ_BILLABLE_EXPENSES] where [FTR_ID] = [EPNS_FTR_ID] GROUP BY [EPNS_FTR_ID]) = 0)

  then 'Non Billable'

  else 'Billable'

  end  as FTR_Billable

  ,(SELECT MIN(ITN_DATE) FROM [FTR].[dbo].[REQ_FTR_ITINERARY] WHERE [ITN_FTR_ID] = [FTR_ID]) AS FTR_FromDate

        ,(SELECT MAX(ITN_DATE) FROM [FTR].[dbo].[REQ_FTR_ITINERARY] WHERE [ITN_FTR_ID] = [FTR_ID]) AS FTR_ToDate

        ,(SELECT ITN_PLACE_FROM FROM [FTR].[dbo].[REQ_FTR_ITINERARY] WHERE [ITN_FTR_ID] = [FTR_ID] and itn_id = (select min(itn_id) from  [FTR].[dbo].[REQ_FTR_ITINERARY] WHERE [ITN_FTR_ID] = [FTR_ID])) AS FTR_ORIGIN

  ,(SELECT ITN_PLACE_TO   FROM [FTR].[dbo].[REQ_FTR_ITINERARY] WHERE [ITN_FTR_ID] = [FTR_ID] and itn_id = (select min(itn_id) from  [FTR].[dbo].[REQ_FTR_ITINERARY] WHERE [ITN_FTR_ID] = [FTR_ID])) AS FTR_DESTINATION

  FROM [FTR].[dbo].[REQ_FTR] A

       inner join [BETI].[dbo].[ZT_EMPLOYEE_MAS] B

  on A.[FTR_DASID] =  B.[DAS_ID])t

  where t.[Ftr_todate]>=getdate()

  )tt

  where tt.rnk=1;

//STORE empOnLeave into 'Z:\Dev\Admin\Dashboards\leave.qvd'(qvd);

ftr_leave:

load

DASID as FTR_DASID,

FTR_FromDate as FromDate,

Ftr_todate as ToDate,

leave_type

Resident empOnLeave;

drop Table empOnLeave;

  Outer Join([tmp MySeatData])

  load *

  Resident ftr_leave;

[Anonymous]

i am fetching data from database

[Anonymous · Accepted solution]

i simply want to concatenate data of ftr and emponleave table and later i want to join it to other table but its not happening

[vijay_iitkgp]

Hope This help A: LOAD * INLINE [ A,B,C 10,15,AA 20,30,PP ]; Concatenate LOAD * INLINE [ A,B,C 40,20,DD 59,90,MM ]; Left Join LOAD * INLINE [ A,D 10,XYZ 20,PQR ];