topic Re: Using concat() in load script? in Connectivity & Data Prep

Using concat() in load script?

bschnug — Thu, 11 Jan 2018 16:03:45 GMT

I am trying to concat a field by another field in my load script, but am running into an issue where the load stops when it gets to this portion. PlacementsTable appears to load fine, then after showing the lines fetched the load just seems to be processing.

Here is an example of my code, one complication is that I am trying to do this after a left join. Is it possible the resident table should be PlacementsTable instead of MainTable?

Unfortunately I cant attach any data for proprietary reasons, so hopefully this is enough for someone to help!

PlacementsTable:

left join (MainTable)

load

text("TDLINX Code") as "TDLINX Code",

Capitalize("Marque Description") as "Marque Description",

"Placement Type"

From *

(qvd);

Concat1:

Load "TDLINX Code", Concat("Placement Type",',') as Placements Resident MainTable Group By "TDLINX Code";

Re: Using concat() in load script?

petter — Thu, 11 Jan 2018 16:12:56 GMT

At first glance this looks perfectly normal and valid. How many rows are there in the two tables?

If you leave out the concat operation how long does the join take? Are you running this in Qlik Sense Desktop or in Qlik Sense Enterprise?

Re: Using concat() in load script?

bschnug — Thu, 11 Jan 2018 16:32:01 GMT

Thanks Petter, MainTable has 13.7M rows and PlacementsTable has 6.7M

I suppose this could just be something that will take a very long time? It only takes approx 10-12 mins to load the join without the concat.

This is enterprise, pointing to two QVDs

Re: Using concat() in load script?

bschnug — Thu, 11 Jan 2018 18:11:03 GMT

I think it may have just been a processing time issue...I ran for 2 hrs (I know, I know) and no progress, so then I tried mixing up the order a bit and got the load to work relatively quickly with the following script:

PlacementsTable:

LOAD

text("TDLINX Code") as "TDLINX Code",

Capitalize("Marque Description") as "Marque Description",

"Placement Type"

FROM [lib://Master Data Source Location (ws-na_singhvi)/MH SF PLACEMENT.QVD]

(qvd);

Concat1:

LOAD "TDLINX Code",Concat("Placement Type",',') as Placements Resident PlacementsTable Group By "TDLINX Code";

NewPlacements:

left join (MainTable)

LOAD

"TDLINX Code",

"Marque Description",

"Placement Type"

Resident PlacementsTable;

Drop table PlacementsTable;

The concatenation is pulling in far more values than I anticipated, but I suppose that is something else entirely. Thanks for the help Petter, I will mark this as closed.

Re: Using concat() in load script?

rubenmarin — Thu, 11 Jan 2018 18:11:15 GMT

Hi Bryan, maybe it's trying to creting a really long strings because after the Join each TDLINX code has many rows. In this case you can try adding Distinct:

Concat1:

Load "TDLINX Code", Concat(Distinct "Placement Type",',')

Re: Using concat() in load script?

bschnug — Thu, 11 Jan 2018 18:15:37 GMT

EDIT: Ruben, this worked great, thanks!

Thanks Ruben, I will try this to solve the issue of my concatenation being far too many values!