Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
andrea0901
Creator
Creator

Concatenation in script

Hi All,

I have a below scenario like :

ORUCompany CodeAP DocumentClearDocValue_DCAmount_in_DCFinal_EUR_WPPFinal EUR
926043C0_UAX0C0_19000043142000013478-19833.75-625.2649189-626.4866779947.219567149
926043C0_UAX0C0_19000043142000013475-5413.9-170.6748217-171.0083179947.219567149
926043C0_UAX0C0_19000043142000013476-4429.55-139.6428926-139.9157529947.219567149
926043C0_UAX0C0_19000043142000013477-369.13-11.63693399-11.6596724947.219567149

 

In the above table, AP Document is same for all 4 rows. only Clear Doc columns are different. so for one AP document i might be having different ClearDoc's. so in that case i need to concatenate all the cleardocs into a single row .

2. and Final EUR has 947.219 which is sum of amount_in_DC only. so i have to get for single AP document all the other columns need to concatneate into single row.

 

Please provide me inputs on this.

 

Thank you.

1 Solution

Accepted Solutions
marcus_sommer

Any error or is table just loaded as it is in Excel? Please check if you really used the right file and if the [AP Document] and ORU have really redundant values to which the other fields could be aggregated.

- Marcus

View solution in original post

9 Replies
marcus_sommer

You could just use something like this:

load [AP Document], concat(ClearDoc, ', ') as ClearDoc, sum(Amount_in_DC) as Amount_in_DC
from Source group by [AP Document];

but are you sure that you need that? Quite often a table like yours could be used directly or with a few adjustments in the datamodel and you could aggregate what you need wihin the UI.

- Marcus

andrea0901
Creator
Creator
Author

hi,

 

when i applied the same code, i am getting error  as "invalid expression" while reloading .

 

Below is my code:

LOAD ORU,
[AP Document],
[Value EUR],
[Amount in DC],
concat(ClearDoc,',') as ClearDoc
FROM
[C:\Users\320033629\Desktop\Book09.xlsx]
(ooxml, embedded labels, table is Sheet1)
Group by [AP Document];

 

marcus_sommer

By an aggregation-load must be each field included within an aggregation-function like min/avg/sum/concat or listed within the group by area. In your case maybe in this way:

LOAD ORU,
[AP Document],
sum([Value EUR]) as [Value EUR],
sum([Amount in DC]) as [Amount in DC],
concat(ClearDoc,',') as ClearDoc
FROM
[C:\Users\320033629\Desktop\Book09.xlsx]
(ooxml, embedded labels, table is Sheet1)
Group by [AP Document], ORU;

- Marcus

Sue_Macaluso
Community Manager
Community Manager

Is this for Qlik Sense or QlikView? I'd like to move it to the appropriate product forum. Thank you
Did you find a solution to your question? Mark the solution as accepted : and if you found it useful, press the like button!
marcus_sommer

Deducing from the provided path it's probably View because in Sense the path starts with lib: unless it's used within the legacy mode which seems rather unlikely in this case.

- Marcus

andrea0901
Creator
Creator
Author

hi,

i have written the same code but clear doc concatenation didn't happen. Please help.

marcus_sommer

Any error or is table just loaded as it is in Excel? Please check if you really used the right file and if the [AP Document] and ORU have really redundant values to which the other fields could be aggregated.

- Marcus

andrea0901
Creator
Creator
Author

hi ,

yes u r correct, the ORU was not redundant. now its working.

 

thank u for your help.

Sue_Macaluso
Community Manager
Community Manager

Thanks!
Did you find a solution to your question? Mark the solution as accepted : and if you found it useful, press the like button!