Can i reduce number of link between sales and AR Table ?

[paulyeo11]

Hi All

I have AR and Sales Table , now both table link by 3 line , which is working fine.

I like to know why i cannot reduce the number of line to 1 ? 

Since i already have ARKey , as i assume that ARKey will link cust_id and company.

My issue is if i remove AR Table cust_id and company field. the AR Table will not able to display cust_id and company info.

Enclosed my image.

[Vegar]

@paulyeo11  you missed to enclose your picture.  It could also be an good idea to post a set of inline tables to materialize  your data model.

[paulyeo11]

Hi Sir

Enclosed my QVW file , hope that you can advise me how to reduce to only 1 line for the link between AR and sales table.

Paul

[Vegar · Accepted solution]

First

Even without the issue of links between AR and Sales you to have circular references in your data model. You do not want circular references.  Read: Understanding circular references

Second: 

In order to remove your syntethic key between AR and Sales you can create a composite key with your key fields. You do also need to decide where you want to keep your COMPANY, CUST_ID (and maybe ARKey?) When you've done that remove the three fields from the table you don't want to keep the values and create a composite field consiting of the three in both tables. 

See how I did in your AR_AGING table below:

AR_AGING:
LOAD 
  autonumber( @1:9T & '_' & 'TDS' )&'|'& @1:9T & left(@10:38T,25) as %AR_AGING,
  //autonumber( @1:9T & '_' & 'TDS' ) as ARKey,// Key to Sales
  //@1:9T as [CUST_ID],	// Kay to Sales
  //left(@10:38T,25) as [COMPANY],// Key to Sales
  'TDS' as SOURCE,	
  @1:9T as [cust_id_ar],
  etc...

 I've attached a qvw with a suggested new link between the two (but I guess you can make it even simpler.)

[paulyeo11]

Hi Sir

Appreciate very much for your sharing.

I must use back the code :-

 autonumber( @1:9T & '_' & 'TDS' ) as ARKey,// Key to Sales

As when i using your propose code :-

autonumber( @1:9T & '_' & 'TDS' )&'|'& @1:9T & left(@10:38T,25) as %AR_AGING,

The AR report does not display all the company.

I think my previous issue is i have remove CUST_ID & COMPANY only for TDS , i forgotten to remove from PMC.

Now it working fine.

Once again thank you very much for your sharing.

Paul Yeo

[paulyeo11]

Hi Sir

You said that I have circular references , I though when have circular references , the table view will show dotted line. But mine seem like no dotted line.

[Vegar]

It depends on which table view you are using. Check out this help page to learn more:
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Table_Viewer.ht...

BR
Vegar

[paulyeo11]

Thank you for your sharing.