Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
Attached is the QVW document. See if this is something you are looking for?
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
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