3 Replies Latest reply: Jul 24, 2015 5:22 AM by Stefan Wühl RSS

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