Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

chris1987
New Contributor II

Concatenate Not Working

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

(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 (qvd) WHERE not Exists(ClearedMasterListID,[AOrder No] & [ALine No]);

STORE Output into (qvd);

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

    drop Table Existing;

2 Replies
girirajsinh
Contributor III

Re: Concatenate Not Working

Output:

add load  * Resident ListOfCleared;

add Load * FROM (qvd) WHERE not Exists([AOrder No] & [ALine No],ClearedMasterListID);

Re: Concatenate Not Working

Maybe is autoconcatenating with previous table, try avoiding this with 'NoConcatenate':

Output:

NoConcatenate

add load  * Resident ListOfCleared;

add Load * FROM (qvd) WHERE not Exists(ClearedMasterListID,[AOrder No] & [ALine No]);

Community Browser