Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Srinivas
Creator
Creator

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
MarcoWedel

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

View solution in original post

5 Replies
JonnyPoole
Employee
Employee

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.

MarcoWedel

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

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

danieloberbilli
Specialist II
Specialist II

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

MarcoWedel

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