topic Merge two lists with similar data type in QlikView

Merge two lists with similar data type

Wed, 15 Dec 2010 20:48:07 GMT

Hello, I'm struggling in Qlikview to do something very simple, so I ask your help:

I need to merge two lists of dates (I have day, month and year in both lists) to a complete list of dates, to use as a graphic dimension. One list is the opening date of a request, and the other is the closing date. The lists are from the same table in two different variables, eg. open_date and close_date. My graphic needs to show all dates as dimension, to count both opened and closed requests on the same dates.

thanks in advance.

Merge two lists with similar data type

Wed, 15 Dec 2010 21:01:40 GMT

The simplest, though perhaps not the most efficient, would be to load open_date and close date in to two different tables then rename them to a common name. You could then concatenate the two tables and then join them back to the original table based on a common key. See below.

tab1:

load widget,
opening_date,
closing_date from widget.csv;

tab2:
load widget,
opening_date as date resident tab1;
concatenate
load widget,
closing_date as date resident tab1;

noconcatenate
final:
load widget,
opening_date,
closing_date resident tab1;
left join
load widget,
date resident tab2;
drop table tab1,tab2;

This would leave you with one table named final with columns for widget, opening_date, closing_date and date. date would be the dimension you could use for your chart. Hope this helps.

Merge two lists with similar data type

Wed, 15 Dec 2010 21:44:07 GMT

thanks tray! I have just changed the variables names in the final: table not to generate $syn tables, and the solution worked just fine!

noconcatenate
final:
load widget,
opening_date as open,
closing_date as close
resident tab1;
left join
load widget,
date resident tab2;
drop table tab1,tab2;

tks a lot