Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
chris1987
Contributor

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]);