See this thread where the same question was asked and answered.
I'm been looking at this code now for a few hours, and been tinkering with it before posting my question. I've written this to where it matches my requirements (From my previous excel, Field = FS Item), but I'm getting constant errors on my join; any ideas?
CrossTable(MMM_FYYY, Data, 4)
LOAD Concat, Country, FSItem,
BA, Jan_FY15, Feb_FY15, Mar_FY15, Apr_FY15, May_FY15, Jun_FY15, Jul_FY15,
Aug_FY15, Sep_FY15, Oct_FY15, Nov_FY15, Dec_FY15, Jan_FY16, Feb_FY16, Mar_FY16, Apr_FY16,
May_FY16, Jun_FY16, Jul_FY16, Aug_FY16, Sep_FY16, Oct_FY16, Nov_FY16, Dec_FY16, Jan_FY17,
Feb_FY17, Mar_FY17, Apr_FY17, May_FY17, Jun_FY17, Jul_FY17, Aug_FY17, Sep_FY17, Oct_FY17,
(ooxml, embedded labels, header is 5 lines, table is Input);
LOAD [FS Items]
(ooxml, embedded labels, table is Sheet1);
LOAD Distinct FSItem
let vfieldnos# = fieldvaluecount('FSItem');
LOAD Distinct [FS Items]
for counter#=0 to ($(vfieldnos#)-1)
let vfield = Peek('FSItem', $(counter#),'Masterfields');
LOAD [FS Items], Value as [$(vfield)]
Resident CommExcel where FSItem = '$(vfield)';
drop table Masterfields;
drop table datagroup;
Thoughts & Suggestions?
Yes, I understand that it can become multiple columns. But how would one do this? To answer your observation, yes, UnitsSold, UnitsInInventory, and PricePerUnit would be defined as columns. But, Qlikview, through my x-table is defining them as a field within a meta-column. E.g. ColData has three data values - UnitsSold, UnitsInInventory, and PricePerUnit; but these should all be their own respective column.
According to the excel data model, the PricePerUnit, UnitsinInventory, and UnitsSold would all be their own specific columns, with all other attributes falling into those through concats, joins, etc. This way, if I wanted to see a chart of PricePerUnit over a given timeframe I can easily create the chart using this one field rather than shifting through a field of fields, because of a bad data model that I cannot change.
NeededDataModel.xlsx 11.9 K
one solution might be:
tabTemp1: CrossTable (MMM_FYYY, Data, 3) LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1371290-301652/DataModel.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Replace(1, bottom, StrCnd(null)),Replace(2, bottom, StrCnd(null)),Replace(3, bottom, StrCnd(null)),Remove(Row, Pos(Top, 2)))); tabTemp2: Generic LOAD Country, Area, Dual(MMM_FYYY,Date#(Replace(MMM_FYYY,'_FY',''),'MMMYY')) as MMM_FYYY, Field, Alt(Data, Ceil(Rand()*1000)) as Data Resident tabTemp1; DROP Table tabTemp1; tabResult: LOAD 1 as TempField AutoGenerate 0; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'tabTemp2.*') THEN JOIN (tabResult) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i DROP Field TempField;
Added some random Data values, so you would just load the plane Data field in tabTemp2 of this example.
Depending on your fiscal year you might have to change the MMM_FYYY field expression as well.
hope this helps
QlikCommunity_Thread_279741.qvw 165.5 K