topic Re: how to combine two fields in QlikView

how to combine two fields

Wed, 19 Dec 2012 07:43:54 GMT

I have a table called "Invocies" in which I have 2 fields, one is INVOICE DATE and another is ORDER DATE. I want to combined the dates and have one field called DATE

Below is the part of the scripting I've done to combine the dates.

Invoices:

LOAD *,

INVDATE as DATE,

ORDDATE as DATE

However I get the following error - "Filed names must be unique with table"

Where am i going wrong in my scripting. Please can you help.

kind regards

Nayan

Re: how to combine two fields

Gysbert_Wassenaar — Wed, 19 Dec 2012 07:55:51 GMT

You can't give two fields the same name. That would make it impossible to know from which field the value should be extracted.

Putting INVDATE and ORDDATE in one field doesn't make sense to me since they are very different things. But if you feel you must you then you need to do something like

Invoices:

LOAD *, INVDATE as DATE from ....etc;

LOAD *, ORDDATE as DATE from .... etc;

That will cause every invoice record to occur twice in the invoices table; once with INVDATE as DATE and once with ORDDATE as DATE. This sounds like a bad idea.

You should probably read this discussion about using common date dimensions and shared calendars.

Re: how to combine two fields

Wed, 19 Dec 2012 08:01:19 GMT

Invoices:

LOAD *,

INVDATE as DATE

outer join

ORDDATE as DATE

Re: how to combine two fields

Wed, 19 Dec 2012 08:05:07 GMT

If u simply need to comine two date fields u can also go with concatente. Otherwise try wat Gysbert has suggested......it shud work

Re: how to combine two fields

Wed, 19 Dec 2012 08:05:24 GMT

Hi Gysbert

Thank you. What im trying to achieve in the front end , i want to see the value of invoices or orders per day in one table, by selecting only one date, in this case its DATE (see table below)

Will it be easier to do it on the front end

DATE	Order Value	Invoiced Value
20121031	200,000	150,000
20121030	500,000	600,000
20121029	3,300,000	4,000,000
20121027	50,000	80,000
20121026	90,000	100,000

kind regards

Nayan

Re: how to combine two fields

Wed, 19 Dec 2012 08:22:57 GMT

Invoices:

LOAD *,

INVDATE as DATE,InvoiceValue from ...

outer join

ORDDATE as DATE,OrderValue from ...

U can have seperate tables

Invoices:

LOAD *,

INVDATE as DATE,InvoiceValue from ...

Orders:

ORDDATE as DATE,OrderValue from....

Re: how to combine two fields

Gysbert_Wassenaar — Wed, 19 Dec 2012 08:28:04 GMT

I thought this discussion looked familiar. Did you follow Miguels advice? You should be all set to go then. Your combined table with orders and invoices will already have a DATE field, as well as the OrderDate and InvoiceDate fields. If you haven't followed his advice yet, do so now.

Re: how to combine two fields

Wed, 19 Dec 2012 08:32:55 GMT

I wil try it. Thank you for your help.

kind regards

Nayan