topic Re: Concatenate tables in QlikView

Concatenate tables

ranibosch — Mon, 23 Oct 2017 07:08:10 GMT

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?

Re: Concatenate tables

prma7799 — Mon, 23 Oct 2017 07:17:19 GMT

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;

Re: Concatenate tables

ranibosch — Mon, 23 Oct 2017 07:45:13 GMT

I get the following error when loading the scrip:

Re: Concatenate tables

prma7799 — Mon, 23 Oct 2017 07:46:49 GMT

Check your file qvd path...

Re: Concatenate tables

ranibosch — Mon, 23 Oct 2017 07:46:58 GMT

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

Re: Concatenate tables

prma7799 — Mon, 23 Oct 2017 07:50:51 GMT

Yeah you can achieve this using Join or Apply map

Re: Concatenate tables

jonathandienst — Mon, 23 Oct 2017 07:54:13 GMT

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";

Re: Concatenate tables

jonathandienst — Mon, 23 Oct 2017 07:56:50 GMT

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_")

Re: Concatenate tables

jonathandienst — Mon, 23 Oct 2017 07:58:31 GMT

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

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

Re: Concatenate tables

prma7799 — Mon, 23 Oct 2017 08:04:56 GMT

Ok Jonathan ...

Thanks

Re: Concatenate tables

ranibosch — Mon, 23 Oct 2017 11:52:01 GMT

Thank you very much..