Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
munnareddy
Contributor

How to do Multiple Cross table based

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

1 Solution

Accepted Solutions

Re: How to do Multiple Cross table based

Hi,

not finished, but maybe helps:

QlikCommunity_Thread_132000_Pic1.JPG.jpg

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

5 Replies
Employee
Employee

Re: How to do Multiple Cross table based

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

Hi,

not finished, but maybe helps:

QlikCommunity_Thread_132000_Pic1.JPG.jpg

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

Not applicable

Re: How to do Multiple Cross table based

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

danieloberbilli
Valued Contributor II

Re: How to do Multiple Cross table based

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

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

Community Browser