Re: Concatenation not working - Qlik Community

Anonymous · ‎2015-04-30

I am having difficulties with the script below as it is not concatenating any fields … I’m sure I have missed something obvious but I’m completely lost … any help would be greatly appreciated. I did a search and have tried adding where 1=1 on the end but that didn't produce any change.

Concatenate (Subleases)

LOAD

#PML_COUNTER as #Sublease_COUNTER,

#PML_COUNTER_2 as #Sublease_COUNTER_2,

[Current Term] as [Sublease Current Term],

[Expiry Date] as [Sublease Expiry Date],

[Final Expiry] as [Sublease Final Expiry],

[Future Terms] as [Sublease Future Terms],

[L Ext Acc] as [SL Ext Acc],

[Lease] as [Sublease],

[Lease Carpark Rent PA] as [Sublease Carpark Rent PA],

[Lease Code] as [SLCode],

[Lease Description] as [Sublease Description],

[Lease Manager] as [Sublease Manager],

[Lease Opex PA] as [Sublease Opex PA],

[Lease Rent PA] as [Sublease Rent PA],

[Lease Start Date] as [Sublease Start Date],

[Lease Status] as [Sublease Status],

[Lease Total Rent PA] as [Sublease Total Rent PA],

[Lease Type] as [Sublease Type],

[Leased Area SQM] as [Subleased Area SQM],

[Leased Carpark Count] as [Subleased Carpark Count],

[PML_CODE] as [SubleaseCode],

[Renewal Notice] as [Sublease Renewal Notice],

PML_SEQ as F_Pm_Sublease_SEQ,

TENANT_CODE as [Sublease Tenant Code],

[Tenant Name] as [Sublease Tenant Name],

[Tenant Email] as [Sublease Tenant Email] ,

Contact_Phone as [Sublease Tenant Phone] ,

Resident Leases where LFlag=1;

beck_bakytbek · ‎2015-04-30

Hi Bella

where is your first table?

Beck

ramoncova06 · ‎2015-04-30

we would have to see the other part of the script to identify if there is any error, though at a quick glance I do not see any problem with it

are the columns being added into that table, but with a null value

Anonymous · ‎2015-04-30

The first table loads fine, I have added the code below. There is no null values the renamed fields match the existing field names in the original table.

Subleases:
LOAD
F_Pm_Sublease_SEQ as SLCode,
F_Pm_Sublease_SEQ,
SubleaseCode,
SubleaseCode&' - '&SubleaseDescription as Sublease,
SubleaseDescription as [Sublease Description],
TypeDescription as [Sublease Type],
CurrentExpiry as [Sublease Expiry Date],
date(FinalExpiry) as [Sublease Final Expiry],
RenewalNotice as [Sublease Renewal Notice],
FutureTerms as [Sublease Future Terms],
ExternalCode as [SL Ext Acc],
date(StartDate) as [Sublease Start Date],
Sublease_SQM as [Subleased Area SQM],
Sublease_CARPARKS as [Subleased Carpark Count],
Sublease_PARKRENTAL as [Sublease Carpark Rent PA],
Sublease_RENTPA as [Sublease Rent PA],
Sublease_OPEXPA as [Sublease Opex PA],
CurrentTerm as [Sublease Current Term],
Sublease_TOTALRENTPA as [Sublease Total Rent PA],
if(Description='Active','Current',if(Description='Inactive','Expired',if(Description='Pending','Proposed',Description))) as [Sublease Status],
[Sublease Tenant Code],
[Sublease Manager];

SQL SELECT
F_Pm_Sublease.F_Pm_Sublease_SEQ,
F_Pm_Sublease.SubleaseCode,
F_Pm_Sublease.SubleaseDescription,
F_Pm_Sublease.CurrentExpiry,
F_Pm_Sublease.CurrentTerm,
F_Pm_Sublease.FinalExpiry,
F_Pm_Sublease.RenewalNotice,
F_Pm_Sublease.ExternalCode,
F_Pm_Sublease.GrossRental,
F_Pm_Sublease.StartDate,
F_Pm_Sublease.FutureTerms,
F_Pm_SubleaseType.TypeDescription,
fn_SubLeaseArea.Sublease_SQM,
fn_SubLeaseCarparks.Sublease_CARPARKS,
fn_SubLeaseRent.Sublease_PARKRENTAL,
fn_SubLeaseRent.Sublease_RENTPA,
fn_SubLeaseRent.Sublease_OPEXPA,
fn_SubLeaseRent.Sublease_TOTALRENTPA,
F_StatusSubLease.Description,
F_Contact.CO_CODE as [Sublease Tenant Code],
F_Contact1.CO_NAME as [Sublease Manager]

FROM F_Pm_Sublease
LEFT OUTER JOIN F_Pm_SubleaseType ON F_Pm_Sublease.F_Pm_SubleaseType_SEQ = F_Pm_SubleaseType.F_Pm_SubleaseType_SEQ
LEFT OUTER JOIN fn_SubLeaseArea() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseArea.F_Pm_Sublease_SEQ
LEFT OUTER JOIN fn_SubLeaseCarparks() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseCarparks.F_Pm_Sublease_SEQ
LEFT OUTER JOIN fn_SubLeaseRent() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseRent.F_Pm_Sublease_SEQ
LEFT OUTER JOIN F_Pm_Tenant ON F_Pm_Sublease.PMT_SEQ = F_Pm_Tenant.PMT_SEQ
LEFT OUTER JOIN F_StatusSubLease ON F_Pm_Sublease.F_StatusSubLease_SEQ = F_StatusSubLease.F_StatusSubLease_SEQ
LEFT OUTER JOIN F_Pm_LeaseManager ON F_Pm_Sublease.F_Pm_LeaseManager_SEQ = F_Pm_LeaseManager.F_Pm_LeaseManager_SEQ
LEFT OUTER JOIN F_Contact F_Contact1 ON F_Pm_LeaseManager.CO_SEQ = F_Contact1.CO_SEQ
LEFT OUTER JOIN F_Contact ON F_Pm_Tenant.CO_SEQ = F_Contact.CO_SEQ;

left join (Subleases)
LOAD Distinct
SLCode,
1 as #Sublease_COUNTER
Resident Subleases;

left join (Subleases)
LOAD Distinct
SLCode,
1 as #Sublease_COUNTER_2
Resident Subleases
where [Sublease Status]='Current';

left join (Subleases)
LOAD
CO_CODE as [Sublease Tenant Code],
CO_NAME as [Sublease Tenant Name],
CO_EMAIL as [Sublease Tenant Email],
CO_PH_BUS as [Sublease Tenant Phone];

SQL SELECT
F_Contact.CO_CODE,
F_Contact.CO_NAME,
F_Contact.CO_EMAIL,
F_Contact.CO_PH_BUS

FROM F_Pm_Tenant
LEFT OUTER JOIN F_Contact ON F_Pm_Tenant.CO_SEQ = F_Contact.CO_SEQ;

ramoncova06 · ‎2015-04-30

I really don't see any error without the exception of you concatenation missing a field for the last ","

"Contact_Phone as [Sublease Tenant Phone] ,

missing field

Resident Leases where LFlag=1;

"

what I normally do in these cases is add an extra field that does not match with the first table, so I can identify what came from where, in this cases I added 'Leases' as Source to the bottom table, I also noticed the left joins you were doing to create the counters, I suggest you create them in you first load instead of doing left joins, this way you can avoid processing overhead and some nasty join issues (I had plenty already)

Subleases:

LOAD

F_Pm_Sublease_SEQ as SLCode,

F_Pm_Sublease_SEQ,

SubleaseCode,

SubleaseCode&' - '&SubleaseDescription as Sublease,

SubleaseDescription as [Sublease Description],

TypeDescription as [Sublease Type],

CurrentExpiry as [Sublease Expiry Date],

date(FinalExpiry) as [Sublease Final Expiry],

RenewalNotice as [Sublease Renewal Notice],

FutureTerms as [Sublease Future Terms],

ExternalCode as [SL Ext Acc],

date(StartDate) as [Sublease Start Date],

Sublease_SQM as [Subleased Area SQM],

Sublease_CARPARKS as [Subleased Carpark Count],

Sublease_PARKRENTAL as [Sublease Carpark Rent PA],

Sublease_RENTPA as [Sublease Rent PA],

Sublease_OPEXPA as [Sublease Opex PA],

CurrentTerm as [Sublease Current Term],

Sublease_TOTALRENTPA as [Sublease Total Rent PA],

if(Description='Active','Current',if(Description='Inactive','Expired',if(Description='Pending','Proposed',Description))) as [Sublease Status],

[Sublease Tenant Code],

[Sublease Manager]

1 as #Sublease_COUNTER

if ([Sublease Status]='Current',1) as #Sublease_COUNTER_2 ;

SQL SELECT

F_Pm_Sublease.F_Pm_Sublease_SEQ,

F_Pm_Sublease.SubleaseCode,

F_Pm_Sublease.SubleaseDescription,

F_Pm_Sublease.CurrentExpiry,

F_Pm_Sublease.CurrentTerm,

F_Pm_Sublease.FinalExpiry,

F_Pm_Sublease.RenewalNotice,

F_Pm_Sublease.ExternalCode,

F_Pm_Sublease.GrossRental,

F_Pm_Sublease.StartDate,

F_Pm_Sublease.FutureTerms,

F_Pm_SubleaseType.TypeDescription,

fn_SubLeaseArea.Sublease_SQM,

fn_SubLeaseCarparks.Sublease_CARPARKS,

fn_SubLeaseRent.Sublease_PARKRENTAL,

fn_SubLeaseRent.Sublease_RENTPA,

fn_SubLeaseRent.Sublease_OPEXPA,

fn_SubLeaseRent.Sublease_TOTALRENTPA,

F_StatusSubLease.Description,

F_Contact.CO_CODE as [Sublease Tenant Code],

F_Contact1.CO_NAME as [Sublease Manager]

FROM F_Pm_Sublease

LEFT OUTER JOIN F_Pm_SubleaseType ON F_Pm_Sublease.F_Pm_SubleaseType_SEQ = F_Pm_SubleaseType.F_Pm_SubleaseType_SEQ

LEFT OUTER JOIN fn_SubLeaseArea() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseArea.F_Pm_Sublease_SEQ

LEFT OUTER JOIN fn_SubLeaseCarparks() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseCarparks.F_Pm_Sublease_SEQ

LEFT OUTER JOIN fn_SubLeaseRent() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseRent.F_Pm_Sublease_SEQ

LEFT OUTER JOIN F_Pm_Tenant ON F_Pm_Sublease.PMT_SEQ = F_Pm_Tenant.PMT_SEQ

LEFT OUTER JOIN F_StatusSubLease ON F_Pm_Sublease.F_StatusSubLease_SEQ = F_StatusSubLease.F_StatusSubLease_SEQ

LEFT OUTER JOIN F_Pm_LeaseManager ON F_Pm_Sublease.F_Pm_LeaseManager_SEQ = F_Pm_LeaseManager.F_Pm_LeaseManager_SEQ

LEFT OUTER JOIN F_Contact F_Contact1 ON F_Pm_LeaseManager.CO_SEQ = F_Contact1.CO_SEQ

LEFT OUTER JOIN F_Contact ON F_Pm_Tenant.CO_SEQ = F_Contact.CO_SEQ;

left join (Subleases)

LOAD

CO_CODE as [Sublease Tenant Code],

CO_NAME as [Sublease Tenant Name],

CO_EMAIL as [Sublease Tenant Email],

CO_PH_BUS as [Sublease Tenant Phone];

SQL SELECT

F_Contact.CO_CODE,

F_Contact.CO_NAME,

F_Contact.CO_EMAIL,

F_Contact.CO_PH_BUS

FROM F_Pm_Tenant

LEFT OUTER JOIN F_Contact ON F_Pm_Tenant.CO_SEQ = F_Contact.CO_SEQ;

Concatenate (Subleases)

LOAD

#PML_COUNTER as #Sublease_COUNTER,

#PML_COUNTER_2 as #Sublease_COUNTER_2,

[Current Term] as [Sublease Current Term],

[Expiry Date] as [Sublease Expiry Date],

[Final Expiry] as [Sublease Final Expiry],

[Future Terms] as [Sublease Future Terms],

[L Ext Acc] as [SL Ext Acc],

[Lease] as [Sublease],

[Lease Carpark Rent PA] as [Sublease Carpark Rent PA],

[Lease Code] as [SLCode],

[Lease Description] as [Sublease Description],

[Lease Manager] as [Sublease Manager],

[Lease Opex PA] as [Sublease Opex PA],

[Lease Rent PA] as [Sublease Rent PA],

[Lease Start Date] as [Sublease Start Date],

[Lease Status] as [Sublease Status],

[Lease Total Rent PA] as [Sublease Total Rent PA],

[Lease Type] as [Sublease Type],

[Leased Area SQM] as [Subleased Area SQM],

[Leased Carpark Count] as [Subleased Carpark Count],

[PML_CODE] as [SubleaseCode],

[Renewal Notice] as [Sublease Renewal Notice],

PML_SEQ as F_Pm_Sublease_SEQ,

TENANT_CODE as [Sublease Tenant Code],

[Tenant Name] as [Sublease Tenant Name],

[Tenant Email] as [Sublease Tenant Email] ,

Contact_Phone as [Sublease Tenant Phone] ,

'Leases' as Source

Resident Leases where LFlag=1;

Anonymous · ‎2015-04-30

Thanks for having a look ... the last ',' is simply cos I removed a couple of lines to post it here. I'm not concerned about the counters they work fine in all of the data models I use, I will try the test field idea but as the table has 100 records before and after I run the script I am working on the assumption that it is not concatenating at all.

ramoncova06 · ‎2015-04-30

I did assume that the ',' was not an issue otherwise you would have gotten an error which but just wanted to be sure

have you tried not concatenating and applying a qualify to your second table to see if you are actually pulling any data at all ?

the issue might be with the where and not with the concatenation

Anonymous · ‎2015-04-30

I have checked the data is there and I am currently trying an option with a temp table to pull the data out and change the fields names before concatenating but no I haven't tied the qualify option.