Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
bigdataxhi
Contributor
Contributor

Dynamic split field into columns in script

Hi there,

I am looking for help on the following issue.

I have a table with many columns which contain multiple-value comma-separated values. The number of comma separated values varies.

  • Fields are as follows
    Project ID
    Project Name
    Project Resources (contain multiple entries)
    Countries covered (contain multiple entries)

I've searched and found entries on subfields, cross table and peek etc, but as I am a newbie, cannot seem to make this work for my data.

Please assist. The sample table is below.

My initial thought is to do the below (but I have no idea how to do this in Qlikview!)

1) Split 'Project Resources' and 'Countries covered' into columns (challenge - variable number of entries - in the full data you can have up to 50 countries covered).

2) Some sort of cross-table, creating a row for each column (challenge - several columns with multiple comma separated values. Might create 'untrue' relationships between the individual values once split).

3) My solution for 2 above is to use the 'Project Resources' and 'Countries covered' as variables.

So, for the below list, for record PPM01 I would expect

   

Project IDProject NameVariableValue
PPM01Project 1Project ResourceTimothy
PPM01Project 1Project ResourceJerry
PPM01Project 1Project ResourceJames
PPM01Project 1Countries coveredThailand
PPM01Project 1Countries coveredMauritania
PPM01Project 1Countries coveredIran
PPM01Project 1Countries coveredCroatia

And finally, I need Project Resource, Countries covered and Project Name to be separate list boxes/ selections on the sheet.

I hope I can find help on here, much anticipating your responses!

   

Project IDProject NameProject ResourcesCountries covered
PPM01Project 1Timothy,Jerry,JamesThailand,Mauritania,Iran,Croatia
PPM02Project 2Karl,Jerry,Timor-Leste
PPM03Project 3Timothy,Jerry,PeterCosta Rica,Saudi Arabia,East Timor (see Timor-Leste),Georgia,Namibia
PPM04Project 4BiJerry,Stephen,Comoros,Moldova,Grenada,Burundi
PPM05Project 5Christian,Harry,Jamaica,Pakistan,Qatar,Panama
PPM06Project 6Victor,Jerry,Cabo Verde,Oman,Afghanistan,Peru,Nigeria
PPM07Project 7BiJerry,Stephen,PeterCambodia,Togo,Liberia,Mauritania,Dominica
PPM08Project 8Karl,Stephen,CaroIsrael,Thailand,Chile,Chad
PPM09Project 9Victor,Stephen,JamesDenmark,Australia,North Korea,Turkmenistan,Portugal
PPM10Project 10Timothy,Stephen,Morocco,Macau,Suriname
PPM11Project 11Christian,Jerry,PeterIndia,Germany
PPM12Project 12Timothy,Harry,Burkina Faso,Korea, South,South Korea,Nauru
PPM13Project 13Christian,Stephen,Tanzania
PPM14Project 14Karl,Stephen,East Timor (see Timor-Leste),Saint Lucia,Israel,Uganda,Italy
PPM15Project 15Victor,Harry,PeterBrunei,South Sudan,Latvia,Nigeria,Saint Lucia
PPM16Project 16Karl,Jerry,JamesBenin,Zambia,Jamaica,Macedonia,New Zealand
PPM17Project 17Precious,Harry,Tunisia
PPM18Project 18Timothy,Jerry,Luxembourg,Latvia,Tanzania,Latvia,Korea, South
PPM19Project 19BiJerry,Harry,Solomon Islands,Hong Kong,Romania,Mongolia
PPM20Project 20Timothy,Stephen,Somalia,Canada,Nauru,China
PPM21Project 21Victor,Harry,JamesNorth Korea,Bahamas, The,Namibia,Indonesia,Montenegro
PPM22Project 22Precious,Harry,Algeria,Nigeria,Kenya,Marshall Islands,Norway
PPM23Project 23Timothy,Jerry,JamesKuwait,Azerbaijan,Iceland,New Zealand,South Korea
PPM24Project 24Timothy,Jerry,Pakistan,Thailand,Ghana,Sri Lanka,Iraq
PPM25Project 25Christian,Harry,Mozambique,Djibouti,Slovenia,Korea, North,Tonga
PPM26Project 26Victor,Jerry,Mauritius,Vietnam,Mozambique,Liberia,Romania
PPM27Project 27Victor,Harry,Mali,Albania,Syria,Venezuela,Honduras
PPM28Project 28Christian,Harry,Libya,Iceland,Hong Kong,Kazakhstan,Botswana
PPM29Project 29Victor,Yemi,New Zealand,Colombia,Ethiopia,Zimbabwe
PPM30Project 30Christian,Harry,Nepal,Bulgaria,Singapore,Aruba,Austria
PPM31Project 31Timothy,Stephen,PeterKazakhstan,Palestinian Territories,Tuvalu,Spain,Serbia
PPM32Project 32Karl,Jerry,Singapore,Ecuador,Lesotho,Macau
PPM33Project 33BiJerry,Yemi,Burkina Faso,Philippines,Vanuatu,Tuvalu,Tanzania
PPM34Project 34Karl,Harry,Morocco,Korea,United Arab Emirates,Brazil
PPM35Project 35Christian,Stephen,PeterItaly,Kuwait,Solomon Islands,Singapore,Cyprus
PPM36Project 36Timothy,Jerry,JamesTurkey,Central African Republic,Brunei,Mali
PPM37Project 37Christian,Jerry,PeterUnited Kingdom,Algeria,Antigua and Barbuda,Belgium,Saint Kitts and Nevis
PPM38Project 38Timothy,Stephen,Micronesia,Serbia,Bulgaria,
PPM39Project 39Precious,Stephen,Bhutan,East Timor (see Timor-Leste),Barbados,Albania,Afghanistan
PPM40Project 40Karl,Jerry,JamesDjibouti,Spain,Mexico,Liechtenstein
PPM41Project 41Christian,Stephen,JamesCentral African Republic,Indonesia,Djibouti,Central African Republic,Belize
PPM42Project 42Precious,Yemi,PeterPapua New Guinea,Uzbekistan,El Salvador,Ireland,Spain
PPM43Project 43BiJerry,Stephen,Germany,Norway,Congo, Republic of the,Benin,Cambodia
1 Solution

Accepted Solutions
hector_munoz
Specialist
Specialist

Hi,

Try this code:

FACTS:

LOAD [Project ID]        AS [FACT Project ID],

     [Project Name]      AS [FACT Project Name],

     [Project Resources] AS [FACT Project Resources],

     [Countries covered] AS [FACT Countries covered]

FROM .\FACTS.xlsx (ooxml, embedded labels, table is FACTS);

PROJECTS:

LOAD DISTINCT [FACT Project ID]   AS [Project ID],

              [FACT Project Name] AS [Project Name]

RESIDENT      FACTS;

RESOURCES:

LOAD DISTINCT [FACT Project ID]                       AS [Project ID],

              SubField([FACT Project Resources], ',') AS Resource

RESIDENT FACTS;

COUNTRIES:

LOAD DISTINCT [FACT Project ID]                       AS [Project ID],

              SubField([FACT Countries covered], ',') AS Country

RESIDENT FACTS;

This is the result:

27-01-2017 15-45-42.png

And this is the model:

27-01-2017 15-46-50.png

I attach the QVW and excel files of the sample. I hope it serves to you...

Regards,

H

View solution in original post

7 Replies
marcus_sommer

I think subfield() would be the best choice to create separate dimension-tables with something like this:

IDtoCountry:

load [Project ID], subfield(Country, ',') as Country from Source;

Without the third parameter within subfield() in a load it will run like a loop and create for each item a record and also duplicate each other field-values in those records. Without any measure-field within your source-table you could do the subfield() there but I believe a split into several associated tables will be more suitable.

- Marcus

hector_munoz
Specialist
Specialist

Hi,

Try this code:

FACTS:

LOAD [Project ID]        AS [FACT Project ID],

     [Project Name]      AS [FACT Project Name],

     [Project Resources] AS [FACT Project Resources],

     [Countries covered] AS [FACT Countries covered]

FROM .\FACTS.xlsx (ooxml, embedded labels, table is FACTS);

PROJECTS:

LOAD DISTINCT [FACT Project ID]   AS [Project ID],

              [FACT Project Name] AS [Project Name]

RESIDENT      FACTS;

RESOURCES:

LOAD DISTINCT [FACT Project ID]                       AS [Project ID],

              SubField([FACT Project Resources], ',') AS Resource

RESIDENT FACTS;

COUNTRIES:

LOAD DISTINCT [FACT Project ID]                       AS [Project ID],

              SubField([FACT Countries covered], ',') AS Country

RESIDENT FACTS;

This is the result:

27-01-2017 15-45-42.png

And this is the model:

27-01-2017 15-46-50.png

I attach the QVW and excel files of the sample. I hope it serves to you...

Regards,

H

ahaahaaha
Partner - Master
Partner - Master

Hi,

Try code like this

LOAD [Project ID],

     [Project Name],

     SubField([Project Resources], ',') as ProjectResources,

     SubField([Countries covered], ',') as CountriesCovered    

FROM

(Your path to the data)

While IterNo() < substringcount([Project Resources] , ',') + 1 Or IterNo() < Substringcount([Countries covered], ',') + 1;

Regards,

Andrey

Anonymous
Not applicable

Something like this perhaps.

load

[Project ID],

[Project Name],

'Project Resources' as Variable,

SubField([Project Resources], ',') as Value

(YourTable)

;


concatenate


load

[Project ID],

[Project Name],

'Countries Covered' as Variable,

SubField([Countries Covered], ',') as Value

(YourTable)

;


bigdataxhi
Contributor
Contributor
Author

Hector, thanks.

Just what I needed!

hector_munoz
Specialist
Specialist

Glad to help you, 😉

vladimir_f
Contributor II
Contributor II

Very useful - thanks!

for my task there more situable form of your script

FACTS:

LOAD [Project ID]        AS [FACT Project ID],

    [Project Name]      AS [FACT Project Name],

    [Project Resources] AS [FACT Project Resources],

    [Countries covered] AS [FACT Countries covered]

FROM .\FACTS.xlsx (ooxml, embedded labels, table is FACTS);

PROJECTS:

LOAD DISTINCT [FACT Project ID] , // AS [Project ID],

              [FACT Project Name] AS [Project Name]

RESIDENT      FACTS;

RESOURCES:

LOAD DISTINCT [FACT Project ID]  ,  //                  AS [Project ID],

              SubField([FACT Project Resources], ',') AS Resource

RESIDENT FACTS;

COUNTRIES:

LOAD DISTINCT [FACT Project ID]   ,   //                AS [Project ID],

              SubField([FACT Countries covered], ',') AS Country

RESIDENT FACTS;

for "TAG"-style  searching