topic Re: Data Model Question in QlikView

Data Model Question

Sun, 17 May 2015 16:09:12 GMT

HI guys,

Here is how it my data set looks like.

Main:

ID Amt Qty Date ShipDate Type

1 101 14 09/10/2011 Sales Order

2 56 4 10/11/2011 11/11/2011 Invoice

3 45 10 11/01/2012 12/12/2012 Invoice

Link:

ID LinkedID

1 2

1 3

2 1

3 1

1. Sales Order (ID :1) has quantity 14.

2. This sales order is partially fulfilled(Quantity: 4) by an invoice (ID: 2) on 10/11/2011.

The ShipDate on this invoice is 11/11/2011.

3. The remaining quantities on the sales order is fulfilled by an Invoice (ID: 3) on 11/01/2012.

The ShipDate this invoice is 12/12/2012.

4. The Link table maintains the double linking between transactions.

5. Sales Order (ID: 1) is linked with 2 invoices (ID: 2 and 3)

6. Invoices (ID: 2 and 3) are linked with one Sales Order (ID : 1)

7. The chart should show the latest shiporder of the Sales Order (ID : 1) which is 12/12/2012.

Type ID OrderDate ShipDate

SalesOrder 1 09/10/2011 12/12/2011

I can change data model if required.

Thanks,

Saurabh

Re: Data Model Question

Sun, 17 May 2015 16:29:57 GMT

If I understand you right.

you don't need to do any modelling as such. The two tables that you have will be linked by ID.

When creating your chart, you should concentrate on how you code your expression.

Example , =if (Date=linkedDate, Max(Shipdate)) (Psudo)

What do you think? let me know

Re: Data Model Question

Sun, 17 May 2015 16:45:32 GMT

Sorry but I did not get it.

my chart should look like this

Type ID OrderDate ShipDate

SalesOrder 1 09/10/2011 02/02/2011

SalesOrder 4 05/11/2013 05/01/2014

Can you please help me understand that how your suggested way will give this chart?

Re: Data Model Question

Sun, 17 May 2015 16:56:38 GMT

Ok, I see, you are wanting to go through all the linked records and get the latest.

for example: ID 1 is linked to IDs 2 and 3 and out of those you want to get the latest ship date.

Am I correct?

Re: Data Model Question

Sun, 17 May 2015 17:06:41 GMT

Yes