Calculated Field in script returns field field not found

[Steamer]

Good Morning

I am trying to get the following script to work but I am having an issue finalizing it. (See below)

In summary, I first load my data file (i.e.- v_FirmAUMGross).

 I  then join 3 tables together(i.e.-Users,Tags and UserTags) to obtain which users will have access to given tags in the QMC Console.

I then filter it by the OSUser function so any individual user will appear as only one line in the joined table

This is fine as this is what I want.

I then do a full join with my data file(ie. v_FirmAUMGross) and it gives me all the columns concatenated.

This is also what I want, as the cartesian of 1 row vs many, leaves me with the entire dataset plus the columns from the original combined “user’/tag” join.

There are no common fields.

My problem occurs when I try and create a calculated field:    IF([Tag_Name]='CMR',[AcctMaskedCodeName],[AcctMaskedNameCode]) as MaskedCodeName

(i.e.-[Tag_Name} is in the Tags table where  [AcctMaskedCodeName] is in the v_FirmAUMGross table)

When  I add this to the load table, it doesn’t recognize the “Tag_Name” field as I get the message “Tag_Name” not found

I've read many posts on this subject  and tried extensively but haven't been able to resolve the issue.

Any help would be greatly appreciated.

Thanks

Steamer

LIB CONNECT TO 'Azure_SQL_Database_dsql-canc-wm-cam-dev-01.database.windows.net (ad_xxx)';

LOAD
[EffectiveDate],
[AcctCode],
[SecId],
[Qty],
[MVGrossCAD],
[AUMGrossCAD],
[ClientName],
[ClientMaskedName],
[ClientEntityType],
[AcctCodeName],
[AcctMaskedCodeName],
[AcctNameCode],
[AcctMaskedNameCode],
[AcctBaseCurrency],
Date([AcctInceptionDate] ) AS [AcctInceptionDate],
[ClientType],
[ClientSubType],
[AccountType],
[AccountSubType],
[MandateType],
[MandateSubType],
[LeadInvestmentManager],
[ServicingManager],
[SecName],
[SecDisplayName],
[SecPMRDisplayName],
[Ticker],
[InvestmentType],
[SecCountryOfIssue],
[SecCurrency],
[SecClassCodeLevel1],
[SecClassCodeLevel2],
[SecClassCodeLevel3],
[RegionLevel3],
[RegionLevel2],
[RegionLevel1],
[GICS1Sector],
[GICS2IndustryGroup],
[GICS3Industry],
[GICS4SubIndustry],
[FTSEIndustrySector],
[FTSEIndustryGroup],
[FTSEIndustrySubGroup];

[v_FirmAUMGross]:

SELECT EffectiveDate,
AcctCode,
SecId,
Qty,
MVGrossCAD,
AUMGrossCAD,
ClientName,
ClientMaskedName,
ClientEntityType,
AcctCodeName,
AcctMaskedCodeName,
AcctNameCode,
AcctMaskedNameCode,
AcctBaseCurrency,
AcctInceptionDate,
ClientType,
ClientSubType,
AccountType,
AccountSubType,
MandateType,
MandateSubType,
LeadInvestmentManager,
ServicingManager,
SecName,
SecDisplayName,
SecPMRDisplayName,
Ticker,
InvestmentType,
SecCountryOfIssue,
SecCurrency,
SecClassCodeLevel1,
SecClassCodeLevel2,
SecClassCodeLevel3,
RegionLevel3,
RegionLevel2,
RegionLevel1,
GICS1Sector,
GICS2IndustryGroup,
GICS3Industry,
GICS4SubIndustry,
FTSEIndustrySector,
FTSEIndustryGroup,
FTSEIndustrySubGroup
FROM "dsqldb-cdf-cami-107014-dev".dbo."v_FirmAUMGross";

LIB CONNECT TO 'PostgreSQL_postgresql-canc-107015-dev-jskrgwjcvs5zs.postgres.database.azur.com';

let varuser=TextBetween(osuser() ,'=','',2);

LOAD ID as UserId,
UserId AS UserName,
UserDirectory,
Name,
RolesString,
ModifiedByUserName,
UserDirectoryConnectorName;

[Users]:
SELECT "ID",
"UserId",
"UserDirectory",
"Name",
"RolesString",
"Inactive",
"ModifiedByUserName",
"UserDirectoryConnectorName"
FROM "public"."Users"where "UserId"='$(varuser)';

LEFT JOIN

LOAD Tag_ID,
User_ID as UserId;

[TagUsers]:

SELECT "Tag_ID",
"User_ID"
FROM "public"."TagUsers";

LEFT JOIN

LOAD ID AS Tag_ID,
Name as Tag_Name;

SELECT "ID",
"Name",
"ModifiedByUserName"
FROM "public"."Tags";

join ([Users])
load *,if([Tag_Name]='CMR',[AcctMaskedCodeName],[AcctMaskedNameCode])
resident v_FirmAUMGross;
drop table v_FirmAUMGross;

[stevejoyce]

it looks like you are joining tag_name into TagUsers (since you are not specifying a table in your join it's the last loaded table), and your if-statement is loading v_FirmAUMGross, which Tag_name doesn't exist in.

you're missing joining tag_name to v_FirmAUMGross

[Steamer]

Hi stevejoyce

Thank you for your response

I am a relative newbie to qlik so forgive me if I need things to be  spelled out to understand the fine detail.

How would you specifically make the changes to:

join ([Users])
load *,if([Tag_Name]='CMR',[AcctMaskedCodeName],[AcctMaskedNameCode])
resident v_FirmAUMGross;
drop table v_FirmAUMGross;

 Would it be join ([Tags]) ?

I am not sure how I would revise:     load *,if([Tag_Name]='CMR',[AcctMaskedCodeName],[AcctMaskedNameCode])

Any other adjustments?

Your help is greatly appreciated

Thx

Steamer

[stevejoyce]

How many TagIDs can a UserID have?

How many TagNames can a TagID have?

[Steamer]

Only 1 UserID per tagname for this project

[Steamer]

Tag id is tied to Tag Name

[stevejoyce]

This would be simplified with applymap, you can look that up on how to use.  But to correct your issue...  You first need to join the tables.  And then you can use the fields in a load statement.

[Users]:
SELECT "ID",
"UserId",
"UserDirectory",
"Name",
"RolesString",
"Inactive",
"ModifiedByUserName",
"UserDirectoryConnectorName"
FROM "public"."Users"where "UserId"='$(varuser)';

LEFT JOIN ([Users])

LOAD Tag_ID,
User_ID as UserId;

LEFT JOIN ([Users])

SELECT "Tag_ID",
"User_ID"
FROM "public"."TagUsers";

LEFT JOIN ([Users])

LOAD ID AS Tag_ID,
Name as Tag_Name;

SELECT "ID",
"Name",
"ModifiedByUserName"
FROM "public"."Tags";

join ([Users])
load *
resident v_FirmAUMGross;
drop table v_FirmAUMGross;

Final_Table:

load *,if([Tag_Name]='CMR',[AcctMaskedCodeName],[AcctMaskedNameCode]) as NewField

resident Users;

drop table Users.

[Steamer]

Thanks stevejoyce

I tried the script as per below but I am getting the messages "users table not found"

LIB CONNECT TO 'Azure_SQL_Database_dsql-canc-wm-cam-dev-01.database.windows.net (ad_solman)';

LOAD
[EffectiveDate],
[AcctCode],
[SecId],
[Qty],
[MVGrossCAD],
[AUMGrossCAD],
[ClientName],
[ClientMaskedName],
[ClientEntityType],
[AcctCodeName],
[AcctMaskedCodeName],
[AcctNameCode],
[AcctMaskedNameCode],
[AcctBaseCurrency],
Date([AcctInceptionDate] ) AS [AcctInceptionDate],
[ClientType],
[ClientSubType],
[AccountType],
[AccountSubType],
[MandateType],
[MandateSubType],
[LeadInvestmentManager],
[ServicingManager],
[SecName],
[SecDisplayName],
[SecPMRDisplayName],
[Ticker],
[InvestmentType],
[SecCountryOfIssue],
[SecCurrency],
[SecClassCodeLevel1],
[SecClassCodeLevel2],
[SecClassCodeLevel3],
[RegionLevel3],
[RegionLevel2],
[RegionLevel1],
[GICS1Sector],
[GICS2IndustryGroup],
[GICS3Industry],
[GICS4SubIndustry],
[FTSEIndustrySector],
[FTSEIndustryGroup],
[FTSEIndustrySubGroup];

[v_FirmAUMGross]:

SELECT EffectiveDate,
AcctCode,
SecId,
Qty,
MVGrossCAD,
AUMGrossCAD,
ClientName,
ClientMaskedName,
ClientEntityType,
AcctCodeName,
AcctMaskedCodeName,
AcctNameCode,
AcctMaskedNameCode,
AcctBaseCurrency,
AcctInceptionDate,
ClientType,
ClientSubType,
AccountType,
AccountSubType,
MandateType,
MandateSubType,
LeadInvestmentManager,
ServicingManager,
SecName,
SecDisplayName,
SecPMRDisplayName,
Ticker,
InvestmentType,
SecCountryOfIssue,
SecCurrency,
SecClassCodeLevel1,
SecClassCodeLevel2,
SecClassCodeLevel3,
RegionLevel3,
RegionLevel2,
RegionLevel1,
GICS1Sector,
GICS2IndustryGroup,
GICS3Industry,
GICS4SubIndustry,
FTSEIndustrySector,
FTSEIndustryGroup,
FTSEIndustrySubGroup
FROM "dsqldb-cdf-cami-107014-dev".dbo."v_FirmAUMGross";

LIB CONNECT TO 'PostgreSQL_postgresql-canc-107014-dev-jskrgwjcvs5zs.postgres.database.azure.com';

[Users]:
SELECT "ID",
"UserId",
"UserDirectory",
"Name",
"RolesString",
"Inactive",
"ModifiedByUserName",
"UserDirectoryConnectorName"
FROM "public"."Users"where "UserId"='$(varuser)';

LEFT JOIN ([Users])

LOAD Tag_ID,
User_ID as UserId;

LEFT JOIN ([Users])

SELECT "Tag_ID",
"User_ID"
FROM "public"."TagUsers";

LEFT JOIN ([Users])

LOAD ID AS Tag_ID,
Name as Tag_Name;

SELECT "ID",
"Name",
"ModifiedByUserName"
FROM "public"."Tags";

join ([Users])
load *
resident v_FirmAUMGross;
drop table v_FirmAUMGross;

Final_Table:

load *,if([Tag_Name]='CMR',[AcctMaskedCodeName],[AcctMaskedNameCode]) as NewField

resident Users;

drop table Users.

[stevejoyce · Accepted solution]

I see one issue, i removed a "join" line in the middle of the preceeding load.  If you still get an error can you see where in the load it is saying it can't find table Users.

LOAD
[EffectiveDate],
[AcctCode],
[SecId],
[Qty],
[MVGrossCAD],
[AUMGrossCAD],
[ClientName],
[ClientMaskedName],
[ClientEntityType],
[AcctCodeName],
[AcctMaskedCodeName],
[AcctNameCode],
[AcctMaskedNameCode],
[AcctBaseCurrency],
Date([AcctInceptionDate] ) AS [AcctInceptionDate],
[ClientType],
[ClientSubType],
[AccountType],
[AccountSubType],
[MandateType],
[MandateSubType],
[LeadInvestmentManager],
[ServicingManager],
[SecName],
[SecDisplayName],
[SecPMRDisplayName],
[Ticker],
[InvestmentType],
[SecCountryOfIssue],
[SecCurrency],
[SecClassCodeLevel1],
[SecClassCodeLevel2],
[SecClassCodeLevel3],
[RegionLevel3],
[RegionLevel2],
[RegionLevel1],
[GICS1Sector],
[GICS2IndustryGroup],
[GICS3Industry],
[GICS4SubIndustry],
[FTSEIndustrySector],
[FTSEIndustryGroup],
[FTSEIndustrySubGroup];

[v_FirmAUMGross]:

SELECT EffectiveDate,
AcctCode,
SecId,
Qty,
MVGrossCAD,
AUMGrossCAD,
ClientName,
ClientMaskedName,
ClientEntityType,
AcctCodeName,
AcctMaskedCodeName,
AcctNameCode,
AcctMaskedNameCode,
AcctBaseCurrency,
AcctInceptionDate,
ClientType,
ClientSubType,
AccountType,
AccountSubType,
MandateType,
MandateSubType,
LeadInvestmentManager,
ServicingManager,
SecName,
SecDisplayName,
SecPMRDisplayName,
Ticker,
InvestmentType,
SecCountryOfIssue,
SecCurrency,
SecClassCodeLevel1,
SecClassCodeLevel2,
SecClassCodeLevel3,
RegionLevel3,
RegionLevel2,
RegionLevel1,
GICS1Sector,
GICS2IndustryGroup,
GICS3Industry,
GICS4SubIndustry,
FTSEIndustrySector,
FTSEIndustryGroup,
FTSEIndustrySubGroup
FROM "dsqldb-cdf-cami-107014-dev".dbo."v_FirmAUMGross";

LIB CONNECT TO 'PostgreSQL_postgresql-canc-107014-dev-jskrgwjcvs5zs.postgres.database.azure.com';

[Users]:
SELECT "ID",
"UserId",
"UserDirectory",
"Name",
"RolesString",
"Inactive",
"ModifiedByUserName",
"UserDirectoryConnectorName"
FROM "public"."Users"where "UserId"='$(varuser)';

LEFT JOIN ([Users])

LOAD Tag_ID,
User_ID as UserId;

SELECT "Tag_ID",
"User_ID"
FROM "public"."TagUsers";

LEFT JOIN ([Users])

LOAD ID AS Tag_ID,
Name as Tag_Name;

SELECT "ID",
"Name",
"ModifiedByUserName"
FROM "public"."Tags";

join ([Users])
load *
resident v_FirmAUMGross;
drop table v_FirmAUMGross;

Final_Table:

load *,if([Tag_Name]='CMR',[AcctMaskedCodeName],[AcctMaskedNameCode]) as NewField

resident Users;

drop table Users.