topic Re: Dates and Crosstables in QlikView

Dates and Crosstables

hobanwashburne — Tue, 16 Sep 2014 18:54:58 GMT

I have a db in the format:

Customer,

Product,

Year,

Month1,

Month2,

Month3...

I know that I will have to use the crosstable function, but when and how can I define the Months as Months so it can be used with an existing Master Calendar?

Re: Dates and Crosstables

MarcoWedel — Tue, 16 Sep 2014 22:15:39 GMT

please post example data.

Re: Dates and Crosstables

fkeuroglian — Wed, 17 Sep 2014 00:33:02 GMT

Hi Scott

see this example, hope this helps

Fernando

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

Re: How can I get one simple month field if I have 2 month fields?

Cross Table in Qlikview « Learn Qlikview

Convert columns into rows while importing from excel??

Re: Dates and Crosstables

JonnyPoole — Wed, 17 Sep 2014 02:31:39 GMT

The crosstable wizard in the 'table files' wizard is helpful for this structure:

1. Here i'm loading a source file with field hearders as you described

2. When i select crosstable, increase the qualifiers to 3

3. The pivotted new table is shown. note that months are showing under column 'Product' . We change that in fnal step

4. Rename Product to Month. Here it is highlighted where i made the change in the script

5. Here is the loaded result

Now to hook this up to a master calendar, it would be good to create dates. Back in the load script i can make a date between month and year through a resident load.

note: i changed the months from month1, month2 to actual month names

To add Calendar:

1. Name the first table ( I called it Temp) in the load script:

Temp:

CrossTable(Month, Data, 3)

LOAD Customer,

Product,

Year,

Jan,

Feb,

Mar

FROM

(ooxml, embedded labels, table is Sheet1);

2. Add the following resident load to build a date field using Year and Month:

Data:

load

Customer,

Product,

date(date#( Year & '/' & Month, 'YYYY/MMM')) as Date,

Data

Resident Temp;

3. Drop the first table 'Temp'

drop table Temp;

4. Now build a calendar table through a resident load loading the unique dates and calculating year, month,quarter and anything else you want off Date. That will ensure consistency.

Calendar:

load distinct

Date,

Year(Date) as Year,

Month(Date) as Month,

QuarterName(Date) as QuarterName

Resident Data;