Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ranibosch
Creator
Creator

Concatenate tables

Hi all,

I'm struggling to concatenate tables in my script and would appreciate your help.

I would like to concatenate my value entries table with my item ledger entries table inorder to form a single table called ValueEntriesVsItemLedgerEntries

My script looks as follows:

//*******************************VALUE ENTRIES**************************************

ValueEntries:

Load

"Document No_" as "Shipment No",

"Document No_" as "Invoice/Credit note No",

"Item No_" as "Item No",

"Sales Amount (Actual)" as "Value Entries Sales Amount",

"Cost Amount (Actual)" AS "Value Entries Cost Amount";

SQL SELECT

    "Document No_",

    "Item No_",

    "Item Ledger Entry Type",

    "Item Ledger Entry Quantity",

    "Sales Amount (Actual)",

    "Cost Amount (Actual)"

      

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Value Entry";

//*****************************ITEM LEDGER ENTRIES**********************************************

ItemLedgerEntries:

LOAD

"Document No_" as "Shipment No",

// Year("Posting Date") as "Year Sold",

// Month("Posting Date") as "Month Sold",

Pick (WildMatch("Entry Type", 0,1,2,3,4,5,6), 'Purchase','Sales','Positive Adjm.','Negative Adjm.','Transfer','Consumption','Output') as "Item Ledger Entry Type",

Quantity as "Tonnage Sold";

SQL SELECT

    "Document No_",

    "Posting Date",

    "Entry Type",

    Quantity

   

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Item Ledger Entry";

Where "Entry Type" =  '1'

How do I concatenate these 2 tables?

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

No problem concatenting the tables, but are you sure this is a useful data model? Simply:

ValueEntries:

Load

"Document No_" as "Shipment No",

...

"Cost Amount (Actual)" AS "Value Entries Cost Amount";

SQL SELECT

    "Document No_",

    "Item No_",

  ...   

  "Cost Amount (Actual)" 

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Value Entry";

Concatenate(ValueEntries)

LOAD

"Document No_" as "Shipment No",

Pick (WildMatch("Entry Type", 0,1,2,3, ... 'Output') as "Item Ledger Entry Type",

Quantity as "Tonnage Sold";

SQL SELECT

    "Document No_",

    "Entry Type",

    Quantity

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Item Ledger Entry";

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

10 Replies
prma7799
Master III
Master III

First try to convert above sql statement into QVD format it will become easy to do concatenate and try below code.

ValueEntriesVsItemLedgerEntries:

Load

"Document No_" as "Shipment No",

"Document No_" as "Invoice/Credit note No",

"Item No_" as "Item No",

"Sales Amount (Actual)" as "Value Entries Sales Amount",

"Cost Amount (Actual)" AS "Value Entries Cost Amount",

"VLE" AS Flag

From ValueEntries.qvd ;

Concatenate (ValueEntriesVsItemLedgerEntries)

LOAD

"Document No_" as "Shipment No",

Pick (WildMatch("Entry Type", 0,1,2,3,4,5,6), 'Purchase','Sales','Positive Adjm.','Negative Adjm.','Transfer','Consumption','Output') as "Item Ledger Entry Type",

Quantity as "Tonnage Sold",

    "Item No_" as "Item No",

    "ILE" as Flag

from ItemLedgerEntries.qvd; 

ranibosch
Creator
Creator
Author

I get the following error when loading the scrip:

CONCATENATE SCRIPT.JPGCONCATENATE ERROR.JPG

prma7799
Master III
Master III

Check your file qvd path...

ranibosch
Creator
Creator
Author

Should  I rather use a join? As I have a common field between the 2 tables and basically just want to add the tonnage sold column to the value entries table..

prma7799
Master III
Master III

Yeah you can achieve this using Join or Apply map

jonathandienst
Partner - Champion III
Partner - Champion III

No problem concatenting the tables, but are you sure this is a useful data model? Simply:

ValueEntries:

Load

"Document No_" as "Shipment No",

...

"Cost Amount (Actual)" AS "Value Entries Cost Amount";

SQL SELECT

    "Document No_",

    "Item No_",

  ...   

  "Cost Amount (Actual)" 

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Value Entry";

Concatenate(ValueEntries)

LOAD

"Document No_" as "Shipment No",

Pick (WildMatch("Entry Type", 0,1,2,3, ... 'Output') as "Item Ledger Entry Type",

Quantity as "Tonnage Sold";

SQL SELECT

    "Document No_",

    "Entry Type",

    Quantity

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Item Ledger Entry";

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

If "Document No_" is a unique key in both tables, then you may want to join rather than concatenate:

Replace

Concatenate(ValueEntries)

with

Left Join (ValueEntries)

If "Document No_" is not  a unique key in both tables, you may want to load them as separate tables (associated via "Document No_")

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

>>First try to convert above sql statement into QVD format...

Why? There is no need for this to answer this post...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
prma7799
Master III
Master III

Ok Jonathan ...

Thanks