2 Replies Latest reply: Jan 19, 2017 2:17 PM by Ruben Marin RSS

    Concatenate Not Working

    Chris Walsh

      Hi,

       

      I have four tables that I'm trying to filter out and then concatenate:

       

      MasterList: This is the master lookup sheet

       

      NewImport: This is the information which download every time a refresh is ran

       

      ListOfCleared: This is the final list of Items filtered Items. What I do in want in this table are all the items is filter out all the rows that exist in MasterList but not in NewImport - This works ok

       

      Output: This is the final table which I intend to STORE, I want to concatenate UKClearedTracker.qvd with ListOfCleared where [AOrder No] & [ALine No] don't exist in UKClearedTracker.qvd, if they do then ignore them.


      Everything is working up the output part, and I'm getting 3 rows, which exist in UKClearedTracker.qvd but not in ListOfCleared.

       

      Any help would be appreciated, here's the code:

       

      Drop tables ListOfCleared,[ListOfCleared-1],UKClearedTracker,[UKClearedTracker-1];

       

      MasterList:

      replace LOAD

           DateAdded,

           ID,

           [Order No] & [Line No] as MasterID,

           Status,

           Cust,

           [Cust Name],

           [Cust PO],

           [Ord Date],

           [Load Date],

           [Req Date],

           [Deliv Date],

           [Order No],

           [Line No],

           LoadDateChg,

           Bookcode,

           [Item No],

           ItemCust,

           Description,

           Generic,

           [Qty Ordered],

           [Qty Open],

           [Sched Batch],

           ProgStage,

           SchedDate,

           [NetPrice(Ea)],

           [Line-Value],

           [FC Prod Hybrid]

      FROM

      [C:\QlikView Development\qvd\UKAgeingArrearsTracker.qvd](qvd);

       

      NewImport:

      Replace Load

           today() as DateAdded,

           [Order No] & [Line No] & IF(today()-[Req Date]<=7,'1to7',if(today()-[Req Date]<=14,'8to14',if(today()-[Req Date]<=21,'15to21',if(today()-[Req Date]<=28,'22to28','Over28')))) as ID,

           [Order No] & [Line No] as BNewArrearsID,

           Cust,

           [Cust Name],

           [Cust PO],

           [Ord Date],

           [Load Date],

           [Req Date],

           [Deliv Date],

           [Order No],

           [Line No],

           LoadDateChg,

           Bookcode,

           [Item No],

           ItemCust,

           Description,

           Generic,

           [Qty Ordered],

           [Qty Open],

           [Sched Batch],

           ProgStage,

           SchedDate,

           [NetPrice(Ea)],

           [Line-Value],

           [FC Prod Hybrid]

      FROM

      $(vUKDataFiles)Data\cushour.txt

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

      where [Req Date]<today();

       

      ListOfCleared:

       

      add LOAD

           today() as ADateAdded,

           ID AS AID,

           [Order No] & [Line No]  as ClearedMasterListID,

           Cust as ACust,

           [Cust Name] as [ACust Name],

           [Cust PO] as [ACust PO],

           [Ord Date] as [AOrd Date],

           [Load Date] as [ALoad Date],

           [Req Date] as [AReq Date],

           [Deliv Date] as [ADeliv Date],

           [Order No] as [AOrder No],

           [Line No] as [ALine No],

           LoadDateChg as ALoadDateChg,

           Bookcode as ABookcode,

           [Item No] as [AItem No],

           ItemCust as AItemCust,

           Description as ADescription,

           Generic as AGeneric,

           [Qty Ordered] as [AQty Ordered],

           [Qty Open] as [AQty Open],

           [Sched Batch] as [ASched Batch],

           ProgStage as AProgStage,

           SchedDate as ASchedDate,

           [NetPrice(Ea)] as [ANetPrice(Ea)],

           [Line-Value] as [ALine-Value],

           [FC Prod Hybrid] as [AFC Prod Hybrid]

           Resident MasterList WHERE Not Exists(BNewArrearsID,[Order No]&[Line No]);

            

      //So far so good

       

       

      //This bit doesn't work

       

      Output:

      add load  * Resident ListOfCleared;

      add Load * FROM [C:\QlikView Development\qvd\UKClearedTracker.qvd](qvd) WHERE not Exists(ClearedMasterListID,[AOrder No] & [ALine No]);

       

       

      STORE Output into [C:\QlikView Development\qvd\UKClearedTracker.qvd](qvd);

      drop Tables [ListOfCleared],[MasterList],[NewImport];

          drop Table Existing;