topic Re: One table, Two Date Fields in QlikView

One table, Two Date Fields

Tue, 07 Oct 2014 12:37:08 GMT

Hi,

I have a table with two date fields. Those date fields can contain different dates...

and I need to analyze in the same chart like this:

Does anyone have a suggestion?

Thanks!!

Re: One table, Two Date Fields

Gysbert_Wassenaar — Tue, 07 Oct 2014 12:42:57 GMT

Transform the data to a table with only one date field.

Table:

LOAD Cta_number,

Production_Date as Date,

'Production' as Type

Value

FROM ...

Concatenate(Table)

LOAD Cta_number,

Order_Date as Date,

'Order' as Type

Value

FROM ...

Then use Type and Date as dimensions.

Re: One table, Two Date Fields

Tue, 07 Oct 2014 15:43:16 GMT

Hi Gysbert,

Doing this way, all row records are being duplicated...

Any suggestion?

Re: One table, Two Date Fields

Gysbert_Wassenaar — Tue, 07 Oct 2014 15:50:52 GMT

Yes. Do it.

Re: One table, Two Date Fields

Clever_Anjos — Tue, 07 Oct 2014 17:40:43 GMT

Gysbert Wassenaar is the simpler, another way is

1) creating an data island with a "Date" field.

2) using this field as dimension into your graph

3) Adjust your expressions to 'link' to that new field

Re: Re: One table, Two Date Fields

Clever_Anjos — Tue, 07 Oct 2014 17:49:17 GMT

pfa

Re: One table, Two Date Fields

MarcoWedel — Tue, 07 Oct 2014 21:41:07 GMT

Hi,

one solution might be also:

//creation of sample data
tabData:
LOAD RecNo() as %key,
     Ceil(Rand()*10) as Cta_number,
     Date(Ceil(Rand()*DayNumberOfYear(Today())+YearStart(Today()))) as Production_Date,
     Date(Ceil(Rand()*DayNumberOfYear(Today())+YearStart(Today()))) as Order_Date,
     Round(Rand()*1000,0.01) as Value
AutoGenerate 100;

tabLink:
CrossTable (DateType, Date)
LOAD %key,
     Production_Date,
     Order_Date
Resident tabData;

tabCalendar:
LOAD *,
     Day(Date) as Day,
     WeekDay(Date) as WeekDay,
     Week(Date) as Week,
     WeekName(Date) as WeekName,
     Month(Date) as Month,
     MonthName(Date) as MonthName,
     Ceil(Month(Date)/3) as Quarter,
     QuarterName(Date) as QuarterName,
     Year(Date) as Year,
     WeekYear(Date) as WeekYear;     
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1<=Today();
LOAD RangeMin(Min(Production_Date),Min(Order_Date)) as MinDate
Resident tabData;

hope this helps

regards

Marco

Re: Re: One table, Two Date Fields

Thu, 09 Oct 2014 11:40:52 GMT

Thank you Clever!

Works fine!