topic Combine 2 tables with 2 different dates as dimension in QlikView

Combine 2 tables with 2 different dates as dimension

Wed, 21 Dec 2016 12:54:42 GMT

Hello Qlik enthusiasts !

I have two chart(table) in my app. One having a date as dimension from a data table and another having 2 dates (selected on a condition) as a dimension from another data table. I want to combine the two tables into one Note: Each having 1 expression. Please see the sample app for details.

My problem is combining 2nd table which has dynamic date data which again comes from 2 different data tables. I cannot able to get into an idea which will solve this problem.

-Jamy.

Re: Combine 2 tables with 2 different dates as dimension

Gysbert_Wassenaar — Thu, 22 Dec 2016 07:32:53 GMT

You can try creating a link table that will give you a new Date field that links the three date fields in the three other tables:

EKKO2:

LOAD *, Autonumber(PurchasingDocument & '|' & VendorID & '|' & GRPostingDate) as %Key RESIDENT EKKO;

DROP TABLE EKKO;

RENAME TABLE EKKO2 TO EKKO;

EKES2:

LOAD *, Autonumber(PurchasingDocument & '|' & ABConfLatest) as %Key RESIDENT EKES;

DROP TABLE EKES;

RENAME TABLE EKES2 TO EKKO;

NCF2:

LOAD *, Autonumber(VendorID & '|' & NPeriod) as %Key RESIDENT NCF.qvd;

DROP TABLE NCF;

RENAME TABLE NCF2 TO NCF

LinkTable:

LOAD DISTINCT %Key, PurchasingDocument, VendorID, GRPostingDate as Date RESIDENT EKKO;

CONCATENATE (LinkTable)

LOAD DISTINCT %Key, PurchasingDocument, ABConfLatest as Date RESIDENT EKES;

CONCATENATE (LinkTable)

LOAD DISTINCT %Key, VendorID, NPeriod as Date RESIDENT NCF;

DROP FIELD PurchasingDocument FROM EKKO, EKES;

DROP FIELD VendorID FROM EKKO, NCF;

Re: Combine 2 tables with 2 different dates as dimension

Thu, 22 Dec 2016 09:04:05 GMT

This is Ok if the concatenation is straight. Please see the order table's dimension. That date is dynamically chosen by a condition. Can this be fixed in script itself ?

Re: Combine 2 tables with 2 different dates as dimension

Thu, 22 Dec 2016 17:10:45 GMT

stalwar1‌ Dude ! Do you have any idea about this ?

-J