topic Re: Concatinating tables in QlikView

Concatinating tables

sajad_manzoor — Wed, 06 Nov 2013 07:06:33 GMT

HI QlikView Masters,

I have following code as:

//------------------------------------------------------------------------------------------------------------------------------

NavSalesCrMemoHeader:

LOAD *;

SQL SELECT *

FROM Navision3Dummy.dbo." Col Pvt_ Ltd_$Sales Cr_Memo Header" ;

Nav4SalesCrMemoHeader:

LOAD *;

SQL SELECT *

FROM Navision4.dbo."Col Private Limited$Sales Cr_Memo Header" ;

concatenate

SQL SELECT *

FROM Navision4.dbo."Luxury Fashion Pvt Ltd$Sales Cr_Memo Header";

SalesCrMemoHeaderSP3:

Load *, '' as junk;

SQL SELECT *

FROM SP3HF2.dbo."Col Trading LLC$Sales Cr_Memo Header";

SalesCrMemoHeader:

Load *,'' as j12,'Navision3.7dummy' as Database

Resident NavSalesCrMemoHeader;

Concatenate

Load *,'' as j13,'Navision4' as Database

Resident Nav4SalesCrMemoHeader;

concatenate

Load *,

'SP3HF2' as Database

resident SalesCrMemoHeaderSP3;

drop field junk;

Why Junk, J12 , J13 are being loaded hear?

Re: Concatinating tables

salto — Wed, 06 Nov 2013 08:10:57 GMT

Hello,

Junk is being dropped in your last sentence so it should not appear.

J12 and J13 are loaded because you are explicitly concatenating the tables and those fields will then remain in the SAlesCrMemoHeader table.

Re: Concatinating tables

sajad_manzoor — Wed, 06 Nov 2013 09:55:06 GMT

HI SALTO,

Exactly what i am asking for is, "Why JUNK, J12, J13 " are needed in this code.

what benefit do we get from them.

Is there any logical reason of using them.

Re: Concatinating tables

salto — Wed, 06 Nov 2013 10:07:14 GMT

Hi,

They do not seem to give you any information, do they? It seems that for any reason they are being used so QlikView does not concatenate tables with the same fields (it can be done with noconcatenate ) but this is just a supposition.

I would not even use them, if you want to have all CrMemoHeaders ina single table there is no need to use such junk fields. Try removing them:

//------------------------------------------------------------------------------------------------------------------------------

NavSalesCrMemoHeader:

LOAD *;

SQL SELECT *

FROM Navision3Dummy.dbo." Col Pvt_ Ltd_$Sales Cr_Memo Header" ;

Nav4SalesCrMemoHeader:

LOAD *;

SQL SELECT *

FROM Navision4.dbo."Col Private Limited$Sales Cr_Memo Header" ;

concatenate

SQL SELECT *

FROM Navision4.dbo."Luxury Fashion Pvt Ltd$Sales Cr_Memo Header";

SalesCrMemoHeaderSP3:

Load *,

SQL SELECT *

FROM SP3HF2.dbo."Col Trading LLC$Sales Cr_Memo Header";

SalesCrMemoHeader:

Load *,

'Navision3.7dummy' as Database

Resident NavSalesCrMemoHeader;

Concatenate

Load *,

'Navision4' as Database

Resident Nav4SalesCrMemoHeader;

concatenate

Load *,

'SP3HF2' as Database

resident SalesCrMemoHeaderSP3;

Hope this helps!

Re: Concatinating tables

jagan — Wed, 06 Nov 2013 10:17:03 GMT

Hi,

I think the tables Navision4.dbo."Luxury Fashion Pvt Ltd$Sales Cr_Memo Header" and SP3HF2.dbo."Col Trading LLC$Sales Cr_Memo Header" having exactly the same columns, if so then the two tables merged into one. To prevent this Junk column is adding in one of the table. Instead of adding additional columns we can also use NoConctenate option, check for help in Qlikview help file.

j13, j12 are used as flags to differentiate from which data source this records are loaded.

Hope this helps you.

Regards,

Jagan.