Combining 3 Tables Issues

Christopher_Lawrence — Tue, 08 Feb 2022 16:03:11 GMT

Hey All,

I am currently attempting to combine three identical tables together, with the only difference being the title of each table houses either US, AU or EU. To make loading easy, I provided the following logic:

Let vLoopRegion1 = 'AU';
Let vLoopRegion2 = 'EU';
Let vLoopRegion3 = 'US';
Let vRegionCount=3;

Let vTableName='InterviewUsers';

Let LoopCounter = 1;
Let vLoopRegion=vLoopRegion1;
for LoopCounter=1 to $(vRegionCount)
Let vLoopRegion=vLoopRegion$(LoopCounter);
Trace --- Loading $(vLoopRegion) ---;
[$(vTableName)]:
LOAD AspNetUserID,
CustomerId,
Email,
ExternalIdentifier,
IsApproved,
IsCandidate,
Montage_aspnet_UsersInRoles_$(vLoopRegion)_OID,
RoleLevel,
RoleName,
RolesId,
Src,
UserInfo_CreatedOn,
UserInfo_ModifiedOn;

SELECT "AspNetUserID",
"CustomerId",
"Email",
"ExternalIdentifier",
"IsApproved",
"IsCandidate",
"Montage_aspnet_UsersInRoles_$(vLoopRegion)_OID",
"RoleLevel",
"RoleName",
"RolesId",
"Src",
"UserInfo_CreatedOn",
"UserInfo_ModifiedOn"
FROM "MHDW"."EDM"."Dim_MO_UserAndRole_$(vLoopRegion)";
Next;

This effectively loads all 3 tables one after another until the loop is complete. It was my understanding that they would auto concatenate, however upon running this, the data model provides this result, opposed to one table with data from all 3 tables (Screenshot attached). Does anyone know why this is happening/how i can fix it?

Re: Combining 3 Tables Issues

chrismarlow — Tue, 08 Feb 2022 19:02:28 GMT

Hi,

I think your field Montage_aspnet_UsersInRoles_$(vLoopRegion)_OID is differently named on the three tables, so the auto concatenate fails. If you alias that field so is the same it may work?

Generally I found when working with Qlik a lot if I wanted to concatenate reliably I would force it by using CONCATENATE ahead of the load ... appreciate gets messy if you are trying to loop, although here if you are only ever going to have 3 regions I am not sure the complexity of having the loop is really worth it.

Cheers,

Chris.

topic Re: Combining 3 Tables Issues in App Development

Combining 3 Tables Issues

Re: Combining 3 Tables Issues