Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
ranibosch
Contributor

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?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Concatenate tables

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
Honored Contributor III

Re: Concatenate tables

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
Contributor

Re: Concatenate tables

I get the following error when loading the scrip:

CONCATENATE SCRIPT.JPGCONCATENATE ERROR.JPG

prma7799
Honored Contributor III

Re: Concatenate tables

Check your file qvd path...

ranibosch
Contributor

Re: Concatenate tables

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
Honored Contributor III

Re: Concatenate tables

Yeah you can achieve this using Join or Apply map

MVP
MVP

Re: Concatenate tables

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

MVP
MVP

Re: Concatenate tables

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

Re: Concatenate tables

>>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
Honored Contributor III

Re: Concatenate tables

Ok Jonathan ...

Thanks