Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

bschnug
New Contributor II

Using concat() in load script?

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";

1 Solution

Accepted Solutions
bschnug
New Contributor II

Re: Using concat() in load script?

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.

5 Replies
MVP
MVP

Re: Using concat() in load script?

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?

bschnug
New Contributor II

Re: Using concat() in load script?

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

bschnug
New Contributor II

Re: Using concat() in load script?

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?

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",',')

bschnug
New Contributor II

Re: Using concat() in load script?

EDIT: Ruben, this worked great, thanks!

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

Community Browser