Dates and Crosstables

[hobanwashburne]

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?

[MarcoWedel]

please post example data.

[fkeuroglian]

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??

[JonnyPoole · Accepted solution]

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;