topic Merging dates from different columns in QlikView

Merging dates from different columns

Tue, 11 Dec 2012 11:06:38 GMT

Below i have 2 columns (tables) . One has Order Date and the other Invoice Date.

Invoice Date
20121209
20121208
20121207
20121206
20121205


Order Date
20121207
20121206
20121205
20121204
20121203
20121130

I want to create a straight table chart, where I combined both the dates and create one column. See below

Combined Date
20121209
20121208
20121207
20121206
20121205
20121204
20121203
20121130

How do I do this

Please help

kind regards

Nayan

Re: Merging dates from different columns

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 11:08:57 GMT

Hi Nayan,

What I would do is to concatenate both tables, and make as many fields as possible to be named alike, but in this case, just renaming the date field to the same should work.

Hope that helps.

Miguel

Re: Merging dates from different columns

sujeetsingh — Tue, 11 Dec 2012 11:46:08 GMT

Just join the tables and drop the basic table and rename the common field except the primary key.

Re: Merging dates from different columns

Tue, 11 Dec 2012 12:25:39 GMT

Hi Miguel

In my model, these dates ie ,Invoice and Order dates are two seperate fields. Im fairly new in tems of QV scripting, how do i use that function to concatenate 2 fields.

kind regards

Nayan

Re: Merging dates from different columns

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 12:30:14 GMT

Hi Nayan,

Concatenate allows you to create one bigger table starting from two smaller tables, useful when building a data model i.e.: a fact table made of orders and invoices. The script looks like

Orders:
LOAD *,
     OrderDate AS Date,
     'Order' AS Source
FROM Orders.qvd (qvd);
Invoices:
CONCATENATE (Orders) LOAD *,
     InvoiceDate AS Date,
     'Invoice' AS Source
FROM Invoices.qvd (qvd);

All fields in "Orders" that do not exist in "Invoices" will be created and populated with null values, and vice versa. In this sample, if you see the model in the Table Viewer, you only see the Orders table with all fields from both tables.

Hope that helps.

Miguel

Re: Merging dates from different columns

Tue, 11 Dec 2012 12:49:42 GMT

Hi Miguel

Thank you. By doing this , do i still maintain the individual colums "Invoice Date" and "Order Date" or do they fall away. The reason when I'm asking, because on the front end, i have tables where i only use "order date" or "invoice date".

kind regards

Nayan

Re: Merging dates from different columns

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 12:53:32 GMT

Nayan,

Using the very same script I used, you will keep the original names but you will have a new field named "Date" that will link both tables, so if you select "11/12/2012" in the Date field, both OrderDate and InvoiceDate will have this value selected.

Hope that makes sense.

Miguel