topic Re: Concatenate in QlikView

Concatenate

absekrafi — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlikview experts,

I am new to Qlikview and I have an issue with "concatenate" function.

I sepearted the code of my concatenate function to a sepearte project, every thing work perfecly but when I merge the "concatenate" code in to my project some fields have null values. the fields are CODUSN, TRANSACTION_DATE, CODEART, CODESART, ....

Enclosed the code (it is the same in both cases) but the result is different:

Directory;

PRODUCTION_REDRESS:

LOAD CODEART,

CODESART,

CODUNT,

CODUSN,

DATPROD AS TRANSACTION_DATE,

OBSERVATION,

QTEPROD,

STOCKFM,

STOCKFNM,

STOCKPHY,

TYPEART

FROM

[..\QVD\PRODUCTION.qvd]

(qvd);

Directory;

CONCATENATE (PRODUCTION_REDRESS)

LOAD

CODPROD AS CODEART,

CODESART,

CODU AS CODUNT,

CODUSN,

DATRED AS TRANSACTION_DATE,

QTERED AS QTEPRODRED

FROM

[..\QVD\REDUSN_U.qvd]

(qvd);

STORE PRODUCTION_REDRESS into [..\QVD\PRODUCTION_REDRESS.qvd] (qvd);

DROP Table PRODUCTION_REDRESS;

Directory;

PRODUCTION_REDRESS:

LOAD CODEART,

CODESART,

CODUNT,

CODUSN,

TRANSACTION_DATE,

OBSERVATION,

QTEPROD,

STOCKFM,

STOCKFNM,

STOCKPHY,

TYPEART,

QTEPRODRED,

NumSum(QTEPRODRED, QTEPROD) AS QUANTPROD

FROM

[..\QVD\PRODUCTION_REDRESS.qvd]

(qvd);

STORE PRODUCTION_REDRESS into [..\QVD\PRODUCTION_REDRESS.qvd] (qvd);

Please help to understand why these fields have empty values.

Thank you in advance,

Kind regards.

Abdallah

Re: Concatenate

shiveshsingh — Mon, 16 Apr 2018 10:57:50 GMT

You are using forced concatenation, the fields which are showing null may not have data corresponding to your QTEPREDRED field. can you share your app to debug?

Re: Concatenate

Peter_Cammaert — Mon, 16 Apr 2018 11:29:38 GMT

If you use CONCATENATE to attach a second table to a first table, then a problem exists when the field lists of both tables do not match. QlikView resolves this by assuming fields with NULL values when those fields aren't explicitly specified.

For example, if I have Table1 consisting of fields A, B & C and I CONCATENATE Table2 consisting of fields A, D & E to Table1, then the resulting table will end up having fields A, B, C, E & A for all rows. The rows that were originally from Table1 will have NULL values for all D & E fields, and the rows that were originally from Table2 will have NULL values in fields B & C.

This clearly demonstrates that within the same table, a database engine (from QV or any RDBMS) cannot really have some columns missing in some rows, while they are available in some other rows. All rows must have the same number and type of columns. But some of them may be empty...

Re: Concatenate

absekrafi — Mon, 16 Apr 2018 12:51:52 GMT

Enclosed the code and the Tables. Thank you in advance.

Re: Concatenate

absekrafi — Mon, 16 Apr 2018 13:20:26 GMT

I have CODUSN common to both Tables.

For the other columns I renaimed some of them.

What I don't understand is why when I copy the code I posted to a new Qlikview project the concatenation works?

Re: Concatenate

absekrafi — Mon, 16 Apr 2018 14:01:27 GMT

could you please explain to me how to debug the application?

I will try from my side also.

Thank you

Re: Concatenate

shiveshsingh — Tue, 17 Apr 2018 05:51:00 GMT

Hi, what are the expressions and dimensions for your chart?

Re: Concatenate

absekrafi — Tue, 17 Apr 2018 08:53:55 GMT

Right now I am displaying the output in simple table.

Once my table get redressed, the dimensions of my chart will be CODUSN, PRODUCT_TYPE (based CODEART and CODESART) and Sum(QUANTPROD)

Re: Concatenate

shiveshsingh — Tue, 17 Apr 2018 10:02:31 GMT

What is the reason behind concatenating these tables?

Your QTEPRODRED(Present in second table) is not having values corresponding to other fields in first table.

Re: Concatenate

absekrafi — Tue, 17 Apr 2018 10:29:39 GMT

Hi Shivesh,

I have 2 tables (PRODUCTION and REDUSN_U). The second is table contains corrections of quantity produced.

Both tables have in common:

CODUSN: factory code

CODUNT: unit code

CODEART: article code

CODESART: sub-article code

I want to concatenate the 2 tables in order to get in one table the original production values plus the rectification in order to display the result in a chart

Re: Concatenate

absekrafi — Tue, 17 Apr 2018 10:55:46 GMT

QTEPROD is a field of PRODCTION Table

QTERED AS QTEPRODRED is a field of REDUSN_U Table

And QUANTPROD is the Sum of both previous fields: NumSum(QTEPRODRED, QTEPROD) AS QUANTPROD

Thanks in advance

Re: Concatenate

shiveshsingh — Tue, 17 Apr 2018 11:28:19 GMT

Then u can join them instead, on the basis of matching fields, you'll get other columns too.

Then you can get the derived field in expression.

Re: Concatenate

absekrafi — Tue, 17 Apr 2018 13:44:41 GMT

Thank you Shivesh,

I did a Join on both Tables and it's working.

When we use concatenate?

Re: Concatenate

shiveshsingh — Wed, 18 Apr 2018 05:50:53 GMT

It depends on the requirement, i generally use it for concatenating several tables to create fact table..

please check in qlik help..it will give u a bigger picture