topic How to do Multiple Cross table based in QlikView

How to do Multiple Cross table based

Srinivas — Thu, 04 Sep 2014 17:33:41 GMT

Hi Dear all,

Please find the attached application here i need cross table.

i want different dimession like Region, Sale,Items,FYmonth,Month,Quarter,Sales...like this way.

Based on my excel i tried but i can't able to do.PLEASE HELP ON THIS.

Regards

Munna

Re: How to do Multiple Cross table based

JonnyPoole — Thu, 04 Sep 2014 18:52:00 GMT

Hi. i took a stab at it. The main question is that your date periods cover different time periods with different granularity. Current month has days, prior 12 months has months and quarterly summaries, prior years only have years.

i just loaded all 'as is' so in a single list box you can pick a prior year and a day from the current month for example.

You'll need to think through how these values are calculated before trying to convert them to dates for example.

But the script technique should help. One of the things i did was convert the days to dates and leave the rest.

Re: How to do Multiple Cross table based

MarcoWedel — Thu, 04 Sep 2014 23:00:15 GMT

Hi,

not finished, but maybe helps:

tabInput:
CrossTable (FieldNameTxt, FieldValue, 3)
LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/602142-123487/Daily1.xlsx] (ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, RowCnd(CellValue, 3, StrCnd(contain, 'Total'))),Replace(1, top, StrCnd(null)),Replace(2, top, StrCnd(null))));

Right Join
LOAD Distinct
  FieldNameTxt,
  If(WildMatch(PurgeChar(FieldNameTxt,'[]'), 'FY (??-??)', '?????.000000'),
  Pick(WildMatch(PurgeChar(FieldNameTxt,'[]'), 'FY (??-??)', '?????.000000'),
  Dual(PurgeChar(FieldNameTxt,'[]'), Mid(FieldNameTxt,6,2)),
  Date(PurgeChar(FieldNameTxt,'[]'))
  ),PurgeChar(FieldNameTxt,'[]')) as FieldName,
  If(WildMatch(PurgeChar(FieldNameTxt,'[]'), 'FY (??-??)', '?????.000000'),
  Pick(WildMatch(PurgeChar(FieldNameTxt,'[]'), 'FY (??-??)', '?????.000000'),
  'FY',
  'Date'
  ),'other') as Dimension
Resident tabInput
Where FieldNameTxt<>'Total';

regards

Marco

Re: How to do Multiple Cross table based

Thu, 04 Sep 2014 23:34:45 GMT

Attached is the QVW document. See if this is something you are looking for?

Re: How to do Multiple Cross table based

danieloberbilli — Fri, 05 Sep 2014 04:49:01 GMT

Marco,

I really like your approach with a very compact usage of wildmatch/pick functions.

I am curious about why you use the 'right join towards the distinct load' in this case? Is this like a double-check to avoid any other fields in the final table? And if so, would you not rather like to see those fields that aren't considered yet?

Kind Regards

Daniel

Re: How to do Multiple Cross table based

MarcoWedel — Fri, 05 Sep 2014 23:43:09 GMT

Hallo Daniel,

I wanted to join the FieldName and Dimension fields to the tabInput table anyhow and so it's just been a nice opportunity to get rid of the crosstable rows created by the Total column of the source xls.

thanks for your reply

Gruß

Marco