topic Re: Complicated cross table in QlikView

Complicated cross table

hobanwashburne — Thu, 17 Sep 2015 19:46:47 GMT

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

Re: Complicated cross table

sunny_talwar — Thu, 17 Sep 2015 19:48:57 GMT

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

Re: Complicated cross table

sudeepkm — Thu, 17 Sep 2015 20:01:18 GMT

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.

Re: Complicated cross table

hobanwashburne — Thu, 17 Sep 2015 20:10:38 GMT

The sample data was loaded inline

Re: Complicated cross table

hobanwashburne — Thu, 17 Sep 2015 20:17:09 GMT

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

Re: Complicated cross table

sudeepkm — Thu, 17 Sep 2015 20:25:50 GMT

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.

Re: Complicated cross table

hobanwashburne — Thu, 17 Sep 2015 20:38:25 GMT

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)

Re: Complicated cross table

sunny_talwar — Fri, 18 Sep 2015 01:31:13 GMT

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;

Re: Complicated cross table

sunny_talwar — Fri, 18 Sep 2015 01:59:40 GMT

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