topic Re: How do you write an expression that will pull all dates for a linked table? in QlikView

How do you write an expression that will pull all dates for a linked table?

Anonymous — Tue, 03 May 2016 19:04:50 GMT

I need to find an expression that will pull all the dates linked to my records billing history.

The ONLY function Only({<[TransactionLinks.Link Type Name]={'PurchaseOrderToReceipt'}>}[TransactionLinks.Txn Date]) pulls dates, but the records will multiple dates are reporting as null.

The MAX function Max({<[TransactionLinks.Link Type Name]={'PurchaseOrderToReceipt'}>}[TransactionLinks.Txn Date]) pulls dates, but the records with multiple dates are reporting the maximum linked date on the PO, but it is not accounting for the individual line items.

The MIN function Min({<[TransactionLinks.Link Type Name]={'PurchaseOrderToReceipt'}>}[TransactionLinks.Txn Date]) pulls dates, but only the earliest date that is linked to a particular PO.

I need to encompass the entire billing history dates on each PO. The goal is to calculate the actual lead time we had vs the estimated lead time. In order to gather that information, we need to have an entire billing history for each item. I attached a sample of how the data is being compiled. Please let me know if you need more details.

Thank you

Re: How do you write an expression that will pull all dates for a linked table?

sunny_talwar — Tue, 03 May 2016 20:50:45 GMT

A lot of the field names you mentioned in the expression above are not even available in the Excel attached. What is the purpose of the Excel?

I am not completely sure what you are trying to do. Would you be able to elaborate, with a better sample?

Re: How do you write an expression that will pull all dates for a linked table?

sunny_talwar — Tue, 03 May 2016 23:45:39 GMT

I understand that you cannot use only because a single line in your table might have more than 1 Txn Date. So this leaves you with two options.

Option 1:

Use Txn Date as a dimension instead of using it as a expression. But this may affect your other expression which you might not want to see on each Txn Date (You can work around that using TOTAL qualifier, but with a TOTAL your values would just repeat.

Option 2: If you wish to see all the Txn Date (and there are multiple of them), you can use Concat() instead of Only. With Concat, you would see multiple Txn Date in the same cell. May not look like, but will show you that there are multiple options.

Concat({<[TransactionLinks.Link Type Name]={'PurchaseOrderToReceipt'}>}[TransactionLinks.Txn Date], ' | ')

I would suggest to may be look into the first option where each row in the table has one and only one Txn Date so that everything is visible.

Now it is for you to decide (based on your requirement) what makes more sense for you.

Re: How do you write an expression that will pull all dates for a linked table?

Anonymous — Wed, 04 May 2016 16:27:29 GMT

Thank you for your help with this!

Both answers worked, but in different ways. I am going to go with your suggestion and use option 1 because it lays out the dates in a column format.