topic Advice of Table Structure in QlikView

Advice of Table Structure

shane_spencer — Wed, 13 Sep 2017 12:44:55 GMT

I've been given an App that I need to do some more development and I'm wanting some advice on how to structure the table(s) to get the data in a format that best suits my purposes. Presently there are two tables - Goods Dispatched and Invoices for these.

As you can see there's a synthetic key which I'm not happy about BUT it does the job. i.e the Company / Country / Year / Month are from BOTH tables, the VAT is from INVOICES and the Intrastat is from GOODS. By selecting from the list boxes the data in the table will show the comparison for specific Months, Company, Country etc. There's even another chart that will show a more detailed breakdown by Invoice number.

If I break the synthetic join the table does not work - even if I use set analysis to equate Goods Month to Invoice Month etc. (Though set analysis works well if I create separate charts with the dimensions specific to the expression).

I was thinking of creating one Table of data instead. But what would be best? just create the missing fields - these are more dimensions such as customer name and tax rate etc - in each data set so they concatenate? or join the Tables using the LINK field (which is essentially an Invoice number that exists in both)? The tricky bit is that the Month/Year in GOODS may be different to the Month/Year in INVOICES for each order as they are not dispatched and billed at exactly the same time so I'm unsure how that would affect the decision.

Really after some advice please.

Re: Advice of Table Structure

prieper — Wed, 13 Sep 2017 12:49:07 GMT

What is the content of your fields, i.e. does YEAR have the exact meaning in both tables (GOODS and INVOICES)?

If not, it may be wise to rename these fields into YEAR_Goods and YEAR_Invoices etc.

Else please post an example

Re: Advice of Table Structure

its_anandrjs — Wed, 13 Sep 2017 12:50:04 GMT

Better you can go for the Concatenate as you see that there are many fields are common but some metric are differs

InvoiceAndGoods:

Load * ,'Invoice' as TabFlag From Invoice;

Concatenate(InvoiceAndGoods)

Load * ,'Goods' as TabFlag From Goods;

It is best approach and when doing reporting use the TabFlag selection for getting values from that table it is easy for you.

Re: Advice of Table Structure

shane_spencer — Wed, 13 Sep 2017 12:54:09 GMT

I've got to be careful what data I post unfortunately. However the Month/Year in GOODS will be date it was dispatched and the Month/Year in INVOICES will be the date it was billed - so it's not exactly the same. But then if I rename them the Chart does not work because there's no synthetic join.

Re: Advice of Table Structure

shane_spencer — Wed, 13 Sep 2017 12:55:26 GMT

Would you concatenate the Month/Year fields?

Re: Advice of Table Structure

its_anandrjs — Wed, 13 Sep 2017 13:00:05 GMT

Make a Key in both tables then and link it with this key and on this separated tables create different Year and Month fields for Invoice and Goods.

Invoice:

Load

Year&'_'&Month&'_'&Period&'_'&Company&'_'&Country&'_'&Link as Key

Year as Invoice_Year,

Month as Invoice_Month,*

From Invoice;

Goods:

Load

Year&'_'&Month&'_'&Period&'_'&Company&'_'&Country&'_'&Link as Key

Year as Goods_Year,

Month as Invoice_Month,*

From Goods;

Re: Advice of Table Structure

Kushal_Chawda — Wed, 13 Sep 2017 13:09:01 GMT

I would advice to create the link table for both the tables

Invoices:

LOAD *,

FROM Invoices;

Goods:

LOAD *,

FROM Invoices;

LinkTable:

LOAD distinct

Key,

Key as Key1,

Year,

Month,

Period ,

Company,

Country,

Link

resident Invoices;

concatenate(LinkTable)

LOAD distinct

Key,

Year,

Month,

Period ,

Company,

Country,

Link

resident Goods

where not exists (Key1,Key);

drop field Key1;

Note:

Please comment Year,Month,Period ,Company,Country,Link fields from both table (Invoices & Goods). I have used LOAD * as I don't know what other fields are there in your table. So it simple means that, keep only measures in Invoices & Goods table as dimensions are added in the link table

Re: Advice of Table Structure

prieper — Wed, 13 Sep 2017 13:20:03 GMT

Hi Spane,

you need to be careful: fields, who are different, should not be matched.
Within QV you should then choose a different name, else you would link dates with dispatch with billing.

Looks as if there is a 1:n-relation in your model, concatenation is not a good idea.

What is the meaning of the "LINK"-field in your datamodel?

Re: Advice of Table Structure

shane_spencer — Wed, 13 Sep 2017 13:25:38 GMT

Having the Year/Month as part of the Key - will that cause problems when I try to do a Reconciliation and the Invoice and Goods months are different?

Re: Advice of Table Structure

shane_spencer — Wed, 13 Sep 2017 13:32:58 GMT

It's a Combination of COMPANY and INVOICE NUMBER. Not sure why these have been combined by the creator but the Invoice Number is the common field between the two sets of data. i.e. For every GOOD dispatched there will be an invoice number, and that same invoice number will be in INVOICES data but perhaps for a different Month/Year.

The idea of the Tool is to compare the Values (currency) of what's been dispatched and what's been billed and the two should balance. The users want to look at this data by narrowing down to certain months, companies, countries etc. but at the same time the Month of billing and dispatch may differ so the comparison may not match. So I've got to look at creating a higher level chart that matches the two sets of values irrespective of month.

Re: Advice of Table Structure

shane_spencer — Wed, 13 Sep 2017 13:46:53 GMT

This created a 3 way link - not sure if this is ok or not.

Re: Advice of Table Structure

Kushal_Chawda — Wed, 13 Sep 2017 14:00:34 GMT

3 way link is ok if Link is created on proper Key fields

Re: Advice of Table Structure

shane_spencer — Wed, 13 Sep 2017 14:08:23 GMT

Ok Thanks. I was thinking of having iLINK in Invoices, gLINK is Goods, and iLINK, gLINK and LINK in the Link Table - that way there would not be a 3-way link. Would that be better? Or would that not work?

Re: Advice of Table Structure

Kushal_Chawda — Wed, 13 Sep 2017 14:33:54 GMT

That all depends on the granularity on which your fact exists. If your invoices are exists till month level and Goods just at Year level then you can create iLINK & gLINK as both the Fact are available at different level, but if your Invoice and Goods Fact are at same level then no need to create the separate link you can go with 3 way link approach

Re: Advice of Table Structure

shane_spencer — Mon, 25 Sep 2017 09:37:06 GMT

Thanks everyone for some great advice. In the end I decided that the LinkTable worked best for the data I had: