topic Re: Concatenation in script in QlikView

Concatenation in script

andrea0901 — Thu, 14 Feb 2019 05:56:02 GMT

Hi All,

I have a below scenario like :

ORU	Company Code	AP Document	ClearDoc	Value_DC	Amount_in_DC	Final_EUR_WPP	Final EUR
926043	C0_UAX0	C0_1900004314	2000013478	-19833.75	-625.2649189	-626.4866779	947.219567149
926043	C0_UAX0	C0_1900004314	2000013475	-5413.9	-170.6748217	-171.0083179	947.219567149
926043	C0_UAX0	C0_1900004314	2000013476	-4429.55	-139.6428926	-139.9157529	947.219567149
926043	C0_UAX0	C0_1900004314	2000013477	-369.13	-11.63693399	-11.6596724	947.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.

Re: Concatenation in script

marcus_sommer — Thu, 14 Feb 2019 07:57:08 GMT

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

Re: Concatenation in script

andrea0901 — Thu, 14 Feb 2019 12:36:09 GMT

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

Re: Concatenation in script

marcus_sommer — Thu, 14 Feb 2019 12:40:31 GMT

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

Re: Concatenation in script

Sue_Macaluso — Thu, 14 Feb 2019 13:43:03 GMT

Is this for Qlik Sense or QlikView? I'd like to move it to the appropriate product forum. Thank you

Re: Concatenation in script

marcus_sommer — Thu, 14 Feb 2019 13:58:19 GMT

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

Re: Concatenation in script

andrea0901 — Thu, 14 Feb 2019 16:11:38 GMT

hi,

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

Re: Concatenation in script

marcus_sommer — Thu, 14 Feb 2019 16:22:55 GMT

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

Re: Concatenation in script

andrea0901 — Thu, 14 Feb 2019 16:46:02 GMT

hi ,

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

thank u for your help.

Re: Concatenation in script

Sue_Macaluso — Fri, 15 Feb 2019 19:00:58 GMT

Thanks!