Concatenate

[absekrafi]

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

[shiveshsingh · Accepted solution]

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?

[Peter_Cammaert]

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...

[absekrafi]

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

[absekrafi]

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?

[absekrafi]

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

I will try from my side also.

Thank you

[shiveshsingh]

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

[absekrafi]

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)

[shiveshsingh]

Hi

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.

[absekrafi]

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