# When concatenate tables, lines are multiplayed

**Aissam Chiki**Jul 24, 2015 4:37 AM

Dear QV users,

I have concatenated three tables. The data of one table(IpRecvg) creates multiple rows of the same original row. I don't know what I am doing wrong. Did I concatened incorrect? The script is:

TempReceivings:

**Sql**

SELECT

UNIT_COST AS R_UNIT_COST,

QUANTITY_RECVD AS R_QUANTITY_RECVD,

EXTENDED_TOTAL AS R_EXTENDED_TOTAL,

PO_NUMBER as R_PO_NUMBER ,

ITEM_NUMBER AS R_ITEM_NUMBER,

INVOICE_NUMBER AS R_INVOICE_NUMBER,

ACCOUNT AS R_ACCOUNT,

JOB_NUMBER AS R_JOB_NUMBER,

DESCRIPTION AS R_DESCRIPTION,

STOCK_NUMBER AS R_STOCK_NUMBER,

'Receivings' as R_TABLE,

DATE_RECEIVED AS R_DATE_RECEIVED

from Jobscope.dbo.IpRecvg

WHERE DATE_RECEIVED >= '20150713' AND INVOICE_NUMBER = '';

AP_RECEIVINGS_TEMP:

noconcatenate **LOAD** *,

Date(ReloadTime(),'YYYYMMDD') as R_DATE_VOUCHERED

Resident TempReceivings;

**drop** table TempReceivings;

left join (AP_RECEIVINGS_TEMP)

**Sql**

SELECT

PO_NUMBER AS R_PO_NUMBER,

VENDOR_NUMBER AS R_VENDOR_NUMBER

from Jobscope.dbo.IPPOITM;

VendorInvoicesTemp:

**Sql**

SELECT

ACCOUNT AS R_ACCOUNT,

AMOUNT_INVOICED AS R_EXTENDED_TOTAL,

DATE_VOUCHERED AS R_DATE_VOUCHERED,

DESCRIPTION AS R_DESCRIPTION,

ITEM_NUMBER AS R_ITEM_NUMBER,

VENDOR_NUMBER AS R_VENDOR_NUMBER,

INVOICE_NUMBER AS R_INVOICE_NUMBER,

PO_NUMBER AS R_PO_NUMBER,

'AP' AS R_TABLE

from Jobscope.dbo.IPINVCE

WHERE DATE_VOUCHERED >= '20140101' AND DATE_VOUCHERED <='20161231';

concatenate (AP_RECEIVINGS_TEMP) **LOAD** * resident VendorInvoicesTemp;

**drop** table VendorInvoicesTemp;

POTemp:

**Sql**

SELECT

ACCOUNT AS R_ACCOUNT,

COMPLETE AS R_COMPLETE,

DESCRIPTION AS R_DESCRIPTION,

EXT_TOTAL_CURR AS R_EXTENDED_TOTAL_PO_ITEM_VALUE,

EXTENDED_TOTAL AS R_EXTENDED_TOTAL_ORIGINAL_CURR,

PO_NUMBER AS R_PO_NUMBER,

ITEM_NUMBER AS R_ITEM_NUMBER,

JOB_NUMBER AS R_JOB_NUMBER,

QUANTITY_ORDERED AS R_QUANTITY_ORDERED,

QUANTITY_RECVD AS R_QUANTITY_RECVD,

STOCK_NUMBER AS R_STOCK_NUMBER,

UNIT_COST AS R_UNIT_COST_ORIGINAL_CURR,

UNIT_COST_CURR AS R_UNIT_COST,

VENDOR_NUMBER AS R_VENDOR_NUMBER,

'OpenPo' AS R_TABLE

//'20250101' as R_DATE_VOUCHERED

from Jobscope.dbo.IPPOITM

WHERE COMPLETE <> 'C';

concatenate (AP_RECEIVINGS_TEMP) **LOAD** *,

(R_QUANTITY_ORDERED-R_QUANTITY_RECVD)*R_UNIT_COST AS R_EXTENDED_TOTAL,

Date(ReloadTime(),'YYYYMMDD') as R_DATE_VOUCHERED

resident POTemp;

**drop** table POTemp;

concatenate (AP_RECEIVINGS_TEMP) **LOAD**

Day as Day_Budget,

DatePerformedFormatQV as R_DATE_VOUCHERED,

WorkingDays,

[Maintenance IT Budget],

[Maintenance Machinery Budget],

[Maintenance Building Budget],

[Maintenance Supplies Budget],

[Tools Budget],

[Communication Expenses Budget],

[CAPEX IT Budget],

[CAPEX IT Actual],

'Budget' as R_TABLE

FROM

[M:\Function\Accounting\06. Controlling\04. Reporting\10. KPI\01. Qlikview Data Input\MFE KPI Data Input.xls]

(biff, embedded labels, table is [IT Maintenance Budget$]);

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

AP_RECEIVINGS:

**LOAD** *,

IF(R_ACCOUNT = 'LB5630-41', 'JJAN',

IF(R_ACCOUNT = 'LB5631-41', 'JJAN',

IF(R_ACCOUNT = 'LB5632-41', 'JJAN',

IF(R_ACCOUNT = 'LB5640-41', 'JJAN',

IF(R_ACCOUNT = 'LB5641-41', 'JJAN',

IF(R_ACCOUNT = 'LB5643-41', 'JJAN',

IF(R_ACCOUNT = 'LB5660-41', 'JJAN',

IF(R_ACCOUNT = 'LB5661-41', 'JJAN',

IF(R_ACCOUNT = 'LB5662-41', 'JJAN',

IF(R_ACCOUNT = 'LB5663-41', 'JJAN',

IF(R_ACCOUNT = 'LB5665-41', 'JJAN',

IF(R_ACCOUNT = 'LB5650-41', 'JJAN',

IF(R_ACCOUNT = 'LB5651-41', 'JJAN',

IF(R_ACCOUNT = 'LB3517-030', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-02', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-031', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-03', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-032', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-04', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-035', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-05', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-04', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-07', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3555-02', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-10', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3555-03', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3508', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-060', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-061', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-03', 'BelowTheLine',

IF(R_ACCOUNT = 'DE7432-51', 'FJAN',

IF(R_ACCOUNT = 'FR7432-51', 'FJAN',

IF(R_ACCOUNT = 'GB7432-51', 'FJAN',

IF(R_ACCOUNT = 'CZ7432-51', 'FJAN',

IF(R_ACCOUNT = 'LB8336-40', 'FJAN',

IF(R_ACCOUNT = 'LB8338-40', 'FJAN',

IF(R_ACCOUNT = 'IN8336-40', 'FJAN',

IF(R_ACCOUNT = 'DE8336-40', 'FJAN',

IF(R_ACCOUNT = 'FR8338-40', 'FJAN',

IF(R_ACCOUNT = 'GB8336-40', 'FJAN',

IF(R_ACCOUNT = 'CZ8336-40', 'FJAN',

IF(R_ACCOUNT = 'LB8431-40', 'FJAN',

IF(R_ACCOUNT = 'LB8432-40', 'FJAN',

IF(R_ACCOUNT = 'LB8434-40', 'FJAN',

IF(R_ACCOUNT = 'DE8431-40', 'FJAN',

IF(R_ACCOUNT = 'DE8434-40', 'FJAN',

IF(R_ACCOUNT = 'FR8431-40', 'FJAN',

IF(R_ACCOUNT = 'FR8434-40', 'FJAN',

IF(R_ACCOUNT = 'GB8431-40', 'FJAN',

IF(R_ACCOUNT = 'GB8434-40', 'FJAN',

IF(R_ACCOUNT = 'CZ8431-40', 'FJAN',

IF(R_ACCOUNT = 'CZ8434-40', 'FJAN',

IF([Maintenance Building Budget], 'JJAN',

IF([Maintenance IT Budget], 'FJAN',

IF([Maintenance Machinery Budget], 'JJAN',

IF([Maintenance Supplies Budget], 'JJAN',

IF([Tools Budget], 'JJAN',

IF([Communication Expenses Budget],'FJAN')))))))))))))))))))))))))))))))))))))))))))))))))))))))))) AS R_BUDGET_MANAGER,

IF(R_ACCOUNT = 'LB5630-41', 'Maintenance Building',

IF(R_ACCOUNT = 'LB5631-41', 'Maintenance Building',

IF(R_ACCOUNT = 'LB5632-41', 'Maintenance Building',

IF(R_ACCOUNT = 'LB5640-41', 'Maintenance Machinery',

IF(R_ACCOUNT = 'LB5641-41', 'Maintenance Machinery',

IF(R_ACCOUNT = 'LB5643-41', 'Maintenance Machinery',

IF(R_ACCOUNT = 'LB5660-41', 'Maintenance Supplies',

IF(R_ACCOUNT = 'LB5661-41', 'Maintenance Supplies',

IF(R_ACCOUNT = 'LB5662-41', 'Maintenance Supplies',

IF(R_ACCOUNT = 'LB5663-41', 'Maintenance Supplies',

IF(R_ACCOUNT = 'LB5665-41', 'Maintenance Supplies',

IF(R_ACCOUNT = 'LB5650-41', 'Tools',

IF(R_ACCOUNT = 'LB5651-41', 'Tools',

IF(R_ACCOUNT = 'LB3517-030', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-02', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-031', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-03', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-032', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-04', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-035', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-05', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-04', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-07', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3555-02', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3501-10', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3555-03', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3508', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-060', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-061', 'BelowTheLine',

IF(R_ACCOUNT = 'LB3517-03', 'BelowTheLine',

IF(R_ACCOUNT = 'DE7432-51', 'Communication Expenses',

IF(R_ACCOUNT = 'FR7432-51', 'Communication Expenses',

IF(R_ACCOUNT = 'GB7432-51', 'Communication Expenses',

IF(R_ACCOUNT = 'CZ7432-51', 'Communication Expenses',

IF(R_ACCOUNT = 'LB8336-40', 'Maintenance IT',

IF(R_ACCOUNT = 'LB8338-40', 'Maintenance IT',

IF(R_ACCOUNT = 'IN8336-40', 'Maintenance IT',

IF(R_ACCOUNT = 'DE8336-40', 'Maintenance IT',

IF(R_ACCOUNT = 'FR8338-40', 'Maintenance IT',

IF(R_ACCOUNT = 'GB8336-40', 'Maintenance IT',

IF(R_ACCOUNT = 'CZ8336-40', 'Maintenance IT',

IF(R_ACCOUNT = 'LB8431-40', 'Communication Expenses',

IF(R_ACCOUNT = 'LB8432-40', 'Communication Expenses',

IF(R_ACCOUNT = 'LB8434-40', 'Communication Expenses',

IF(R_ACCOUNT = 'DE8431-40', 'Communication Expenses',

IF(R_ACCOUNT = 'DE8434-40', 'Communication Expenses',

IF(R_ACCOUNT = 'FR8431-40', 'Communication Expenses',

IF(R_ACCOUNT = 'FR8434-40', 'Communication Expenses',

IF(R_ACCOUNT = 'GB8431-40', 'Communication Expenses',

IF(R_ACCOUNT = 'GB8434-40', 'Communication Expenses',

IF(R_ACCOUNT = 'CZ8431-40', 'Communication Expenses',

IF(R_ACCOUNT = 'CZ8434-40', 'Communication Expenses')))))))))))))))))))))))))))))))))))))))))))))))))))) AS R_COST_GROUPING,

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

IF(R_ACCOUNT = 'LB5630-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5631-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5632-41', R_EXTENDED_TOTAL))) as MaintenanceBuildingActual,

IF(R_ACCOUNT = 'LB5640-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5641-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5643-41', R_EXTENDED_TOTAL))) as MaintenanceMachineryActual,

IF(R_ACCOUNT = 'LB5660-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5661-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5662-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5663-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5665-41', R_EXTENDED_TOTAL))))) as MaintenanceSuppliesActual,

IF(R_ACCOUNT = 'LB5650-41', R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB5651-41', R_EXTENDED_TOTAL)) as ToolsActual,

IF(R_ACCOUNT = 'LB8336-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB8338-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'IN8336-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'DE8336-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'FR8338-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'GB8336-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'CZ8336-40',R_EXTENDED_TOTAL))))))) as MaintenanceITActual,

IF(R_ACCOUNT = 'DE7432-51',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'FR7432-51',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'GB7432-51',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'CZ7432-51',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB8431-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB8432-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'LB8434-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'DE8431-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'DE8434-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'FR8431-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'FR8434-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'GB8431-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'GB8434-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'CZ8431-40',R_EXTENDED_TOTAL,

IF(R_ACCOUNT = 'CZ8434-40',R_EXTENDED_TOTAL))))))))))))))) as CommunicationExpensesActual,

if(R_TABLE= 'AP','Invoiced',

if(R_TABLE= 'OpenPo','OpenPo',

if(R_TABLE= 'Receivings','OpenPo'))) AS INVOICE_MATCHED,

MID(R_DATE_VOUCHERED,1,4) as YEAR_VOUCHERED,

Week(MakeDate(Mid(R_DATE_VOUCHERED,1,4),Mid(R_DATE_VOUCHERED,5,2),Mid(R_DATE_VOUCHERED,7,2))) as WEEK_VOUCHERED,

DATE#(MID(R_DATE_VOUCHERED,3,2)&'-'&(MID(R_DATE_VOUCHERED,5,2)),'YY-MM') as ROLLING_MONTH_VOUCHERED,

MakeDate(Mid(R_DATE_VOUCHERED,1,4),Mid(R_DATE_VOUCHERED,5,2),Mid(R_DATE_VOUCHERED,7,2)) as DAY_VOUCHERED

Resident AP_RECEIVINGS_TEMP;

**DROP** TABLE AP_RECEIVINGS_TEMP;

VendorMasterOverhead:

**Sql**

SELECT

VENDOR_NUMBER AS R_VENDOR_NUMBER,

VENDOR_NAME AS R_VENDOR_NAME

from Jobscope.dbo.IPVENDM;

With kind regards,

Aissam