Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bruce_sorge
Contributor III
Contributor III

Concatenating URL string with field

Greetings,

I have a field that needs to be part of a URL string, but the actual URL is  not stored in our database. I am trying this in my load script:

'https://cfpub.epa.gov/ncer_abstracts/index.cfm/fuseaction/display.abstractDetail/abstract/'&[ABSTRAC...' as ReportURL;

Abstract_ID is the field name. When I run the load script, it doesn't return any errors, but yet the URL field isn't added to the table. I searched Google and the discussion boards but I can't find exactly what I am looking for. I also tried to concatenate the URL with the field name in an expression, but I get the invalid dimension message. I was trying this in the expression:

Concat('https://cfpub.epa.gov/ncer_abstracts/index.cfm/fuseaction/display.abstractDetail/abstract/'&ABSTRACT...')

Thanks in advance

 

Bruce

 

Labels (4)
1 Solution

Accepted Solutions
ToniKautto
Employee
Employee

I can't see anything wrong with that snippet, it is a correct way to concatenate string in a LOAD statement. You can see that it works by running the below script. I would also expect that if you run only the script part that you shared it woudl load successfully with all the fields?

Reading the description again, you say that "URL field isn't added to the table". Is it possible that you process the table later in the scripts in a way that excludes the field form the end result?

LOAD 
'https://cfpub.epa.gov/ncer_abstracts/index.cfm/fuseaction/display.abstractDetail/abstract/'&[ABSTRACT_ID]&'/report/' AS ReportURL
Inline [
ABSTRACT_ID
1
2
3
]

 

View solution in original post

4 Replies
ToniKautto
Employee
Employee

Can you share the load script to better understand what might be causing the issue?

bruce_sorge
Contributor III
Contributor III
Author

Sure. It's a very long script (huge DB) so I am going to just post the pertinent details.

 

Set dataManagerTables = 'LOCATION','CONTACT','INSTITUTION','APU_INST_ASSOC','CENTER_INST_ASSOC', 'CENTERS','CENTER_GROUP', 'RFA','ABSTRACT_PROGRESS_UPDATE','ABSTRACT','ABSTR_RES_CAT_ASSOC','RESEARCH_CATEGORY','RFA_RESEARCH_CAT_ASSOC','APU_PUB_ASSOC','PUBLICATION','ZIP_CONGR_ASSOC','CONGRESSIONAL_DISTRICT','URL';

 

[ABSTRACT]:
LOAD
[ABSTRACT_ID],
[ABS_TYPE] as AbstractType,
[ABS_TITLE] as AbstractTitle,
Date([PROJ_START_DT], 'MM/DD/YYYY') as AbsProjStartdate,
Date([PROJ_END_DT], 'MM/DD/YYYY') as AbsProjEndDate,
[RFA_ID],
[EPA_ID] as GrantNumber,
Date([EXT_END_DT], 'MM/DD/YYYY') as AbsExtendedDate,
[FUNDING_AMT] as FundingAmount,
[RESEARCH_CATEGORY] as ResearchCategory,
[STAR] AS StarGrant,
[ALTERNATE_EPA_ID],
[SBIR_PHASE],
[CTR_ABSTRACT_ID],
[MAIN_ABSTRACT_ID],
[AWARDED_BY_GAD],
Date([DATE_AWARDED], 'MM/DD/YYYY'),
[ABS_SUB_TYPE],
'https://cfpub.epa.gov/ncer_abstracts/index.cfm/fuseaction/display.abstractDetail/abstract/'&[ABSTRAC...' as ReportURL;
SELECT "ABSTRACT_ID",
"ABS_TYPE",
"ABS_TITLE",
"PROJ_START_DT",
"PROJ_END_DT",
"RFA_ID",
"EPA_ID",
"EXT_END_DT",
"FUNDING_AMT",
"RESEARCH_CATEGORY",
"STAR",
"ALTERNATE_EPA_ID",
"SBIR_PHASE",
"CTR_ABSTRACT_ID",
"MAIN_ABSTRACT_ID",
"AWARDED_BY_GAD",
"DATE_AWARDED",
"ABS_SUB_TYPE"
FROM "NCERDB"."ABSTRACT";

ToniKautto
Employee
Employee

I can't see anything wrong with that snippet, it is a correct way to concatenate string in a LOAD statement. You can see that it works by running the below script. I would also expect that if you run only the script part that you shared it woudl load successfully with all the fields?

Reading the description again, you say that "URL field isn't added to the table". Is it possible that you process the table later in the scripts in a way that excludes the field form the end result?

LOAD 
'https://cfpub.epa.gov/ncer_abstracts/index.cfm/fuseaction/display.abstractDetail/abstract/'&[ABSTRACT_ID]&'/report/' AS ReportURL
Inline [
ABSTRACT_ID
1
2
3
]

 

bruce_sorge
Contributor III
Contributor III
Author

Got it figured out. When you mentioned another script changing my table, I checked and for some reason a bunch of temp tables were being created and destroyed. I removed all of those and it's working like a champ. 

 

Thanks