Complicated cross table

[hobanwashburne]

I'm having trouble with a cross table similar to the one in the attachment. The data contains a field designating the fiscal year and separate fields containing the budget for each period of that year. I would like to 'read' the fiscal year field value and use it to create a date associated with the appropriate value so that it may be linked to a master calendar.  I have included a sample data set and an image of the desired output in the qvw.

Message was edited by: Scott Wisker

[sunny_talwar]

I only see a qvw file. Can you attach the datasource?

[sudeepkm]

I see you have attached an image to the text object. Assuming your data being present currently as per the image.

I think you can have two load statements. One load to load the account and fiscal year and other info and another load to bring account and cross table data of actual. Assuming account to be the associated field between two tables.

if possible post sample data.

[hobanwashburne]

The sample data was loaded inline

[hobanwashburne]

Sample data was loaded inline and also now attached to the original post

[sudeepkm]

please find the attachment.

you can load the fields upto UC04, FY as normal and then rest of the fields from B01 till B12 as DateTime field and values as Data.

[hobanwashburne]

Not quite,

The fields FY & B01 - B12 shouldn't appear in the final data set at all.

Somehow, I need to convert FY= 2015 and B01 = 3,260 into 4/1/2015 = 3,260 by reading 2015 from the field FY and translating B01 into the first period of the fiscal year (in this case April)

[sunny_talwar]

Is this what you looking for? (Can't really see you QVW file because I am working with Personal Edition of QlikView)

Script:

Table:

CrossTable(Month, Data, 7)

LOAD Company,

     FY,

     Acct,

     UC01,

     UC02,

     UC03,

     UC04,

     B01,

     B02,

     B03,

     B04,

     B05,

     B06,

     B07,

     B08,

     B09,

     B10,

     B11,

     B12

FROM

Data_Test_02.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

LOAD *,

  Month(Date) as Month,

  Year(Date) as FY;

LOAD Company,

  Acct,

  UC01,

     UC02,

     UC03,

     UC04,

     Data,

     MakeDate(FY,

     Pick(WildMatch(Month, '*1', '*2', '*3', '*4', '*5', '*6', '*7', '*8', '*9', '*10', '*11', '*12'),

      4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3), 1) as Date

Resident Table;

DROP Table Table;

[sunny_talwar · Accepted solution]

Discard my previous response. See if this is what you want:

Script:

Table:

CrossTable(Month, Data, 7)

LOAD Company,

     FY,

     Acct,

     UC01,

     UC02,

     UC03,

     UC04,

     B01,

     B02,

     B03,

     B04,

     B05,

     B06,

     B07,

     B08,

     B09,

     B10,

     B11,

     B12

FROM

Data_Test_02.xlsx

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD Company,

  Acct,

  UC01,

     UC02,

     UC03,

     UC04,

     Data,

     MakeDate(If(WildMatch(Month, '*10', '*11', '*12'), FY + 1, FY),

     Pick(WildMatch(Month, '*10', '*11', '*12', '*1', '*2', '*3', '*4', '*5', '*6', '*7', '*8', '*9'),

      1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), 1) as Date

Resident Table;

NewTable:

LOAD *,

  Month(Date) as Month,

  Year(Date) as FY,

  AutoNumber(MonthName(Date)) as Sort

Resident Temp

Order By Date;

DROP Tables Temp, Table;

The view you wanted can be created using a pivot table like this:

Let us know if this is not what you want.

Best,

Sunny