Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

When concatenate tables, lines are multiplayed

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

(
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

1 Solution

Accepted Solutions
MVP
MVP

Re: When concatenate tables, lines are multiplayed

You are doing a Left Join here


left join (AP_RECEIVINGS_TEMP)
Sql
SELECT
PO_NUMBER AS R_PO_NUMBER,
VENDOR_NUMBER AS R_VENDOR_NUMBER
from Jobscope.dbo.IPPOITM;


which can potentially multiply records when you have not a 1:1 relation using your key.

3 Replies
MVP
MVP

Re: When concatenate tables, lines are multiplayed

You are doing a Left Join here


left join (AP_RECEIVINGS_TEMP)
Sql
SELECT
PO_NUMBER AS R_PO_NUMBER,
VENDOR_NUMBER AS R_VENDOR_NUMBER
from Jobscope.dbo.IPPOITM;


which can potentially multiply records when you have not a 1:1 relation using your key.

Not applicable

Re: When concatenate tables, lines are multiplayed

That's the problem :-).  Field PO_NUMBER is not unique. How can I script the left Join table, that I only have one PO_NUMBER in the table?

MVP
MVP

Re: When concatenate tables, lines are multiplayed

You can try like

left join (AP_RECEIVINGS_TEMP)


LOAD PO_NUMBER AS R_PO_NUMBER,

          PO_NUMBER AS CHECK,

       R_VENDOR_NUMBER

WHERE NOT EXISTS(CHECK, PO_NUMBER);
Sql
SELECT
PO_NUMBER,
VENDOR_NUMBER AS R_VENDOR_NUMBER
from Jobscope.dbo.IPPOITM;


DROP FIELD CHECK;

Community Browser