Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
melharts
Contributor
Contributor

Split a column into fields

Good day to you all,

I'm having an issue with trying to split a field (column) into multiple fields.  Normally the subfield would be great for this, but the issue is these fields are not delimited, and I cannot change my data model as the data source is a pre-generated file.  Currently, my column has data such as:

UnitsSold

UnitsinInventory

PricePerUnit

Attached is the pre-generated and unchangeable data model.  I am already using a crosstable to bring the dates into their own respective 'time' field.

How would I split these different fields, into their respective fields, as would be apparent in a SQL database per se?

Thank you for your assistance!

-Mikael

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_279741_Pic1.JPG

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.

see also:

Qlik Design Blog

Use cases for Generic Load | Qlikview Cookbook

hope this helps

regards

Marco

View solution in original post

9 Replies
niclaz79
Partner
Partner

Hi,

See this thread where the same question was asked and answered.

https://community.qlik.com/message/78337?&_ga=2.175446689.2016309852.1509356371-682289330.1470036878...

Best regards,

Niclas Anderström

d_prashanthredd
Creator III
Creator III

Hi Mikael,

You can split column into multiple columns based on values/data.

Is UnitsSold is a value or a column? I feel its a column and holding different values.

If you have values in a structured way, then you can split and name them as you want.

melharts
Contributor
Contributor
Author

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.

Thoughts?

melharts
Contributor
Contributor
Author

Also, I've managed to break out the rows into columns using a generic load; but now my table is ungrouped, is there an easy way to join the data groups back together?  

avinashelite

What is the output format your looking for ??

sujeetsingh
Master III
Master III

My initial question is what is the pattern which tells about in which fragment we will break down or split the fields.

melharts
Contributor
Contributor
Author

Niclas,

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?

CommExcel:

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,

     Nov_FY17, Dec_FY17

FROM [\\...\Comm.xlsx]

(ooxml, embedded labels, header is 5 lines, table is Input);

datagroup:

LOAD [FS Items]

FROM [\\...\datagroups.xlsx]

(ooxml, embedded labels, table is Sheet1);

Masterfields:

LOAD Distinct FSItem

Resident CommExcel;

let vfieldnos# = fieldvaluecount('FSItem');

fields:

LOAD Distinct [FS Items]

Resident datagroup;

let counter#=0;

for counter#=0 to ($(vfieldnos#)-1)

    let vfield = Peek('FSItem', $(counter#),'Masterfields');

join(fields)

LOAD [FS Items], Value as [$(vfield)]

Resident CommExcel where FSItem = '$(vfield)';

next counter#

drop table Masterfields;

drop table datagroup;

Thoughts & Suggestions?

Thanks!

-Mikael

melharts
Contributor
Contributor
Author

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.

Cheers!

MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_279741_Pic1.JPG

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.

see also:

Qlik Design Blog

Use cases for Generic Load | Qlikview Cookbook

hope this helps

regards

Marco

View solution in original post