topic Re: Dynamic split field into columns in script in QlikView

Dynamic split field into columns in script

bigdataxhi — Fri, 27 Jan 2017 12:48:05 GMT

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 ID	Project Name	Variable	Value
PPM01	Project 1	Project Resource	Timothy
PPM01	Project 1	Project Resource	Jerry
PPM01	Project 1	Project Resource	James
PPM01	Project 1	Countries covered	Thailand
PPM01	Project 1	Countries covered	Mauritania
PPM01	Project 1	Countries covered	Iran
PPM01	Project 1	Countries covered	Croatia

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 ID	Project Name	Project Resources	Countries covered
PPM01	Project 1	Timothy,Jerry,James	Thailand,Mauritania,Iran,Croatia
PPM02	Project 2	Karl,Jerry,	Timor-Leste
PPM03	Project 3	Timothy,Jerry,Peter	Costa Rica,Saudi Arabia,East Timor (see Timor-Leste),Georgia,Namibia
PPM04	Project 4	BiJerry,Stephen,	Comoros,Moldova,Grenada,Burundi
PPM05	Project 5	Christian,Harry,	Jamaica,Pakistan,Qatar,Panama
PPM06	Project 6	Victor,Jerry,	Cabo Verde,Oman,Afghanistan,Peru,Nigeria
PPM07	Project 7	BiJerry,Stephen,Peter	Cambodia,Togo,Liberia,Mauritania,Dominica
PPM08	Project 8	Karl,Stephen,Caro	Israel,Thailand,Chile,Chad
PPM09	Project 9	Victor,Stephen,James	Denmark,Australia,North Korea,Turkmenistan,Portugal
PPM10	Project 10	Timothy,Stephen,	Morocco,Macau,Suriname
PPM11	Project 11	Christian,Jerry,Peter	India,Germany
PPM12	Project 12	Timothy,Harry,	Burkina Faso,Korea, South,South Korea,Nauru
PPM13	Project 13	Christian,Stephen,	Tanzania
PPM14	Project 14	Karl,Stephen,	East Timor (see Timor-Leste),Saint Lucia,Israel,Uganda,Italy
PPM15	Project 15	Victor,Harry,Peter	Brunei,South Sudan,Latvia,Nigeria,Saint Lucia
PPM16	Project 16	Karl,Jerry,James	Benin,Zambia,Jamaica,Macedonia,New Zealand
PPM17	Project 17	Precious,Harry,	Tunisia
PPM18	Project 18	Timothy,Jerry,	Luxembourg,Latvia,Tanzania,Latvia,Korea, South
PPM19	Project 19	BiJerry,Harry,	Solomon Islands,Hong Kong,Romania,Mongolia
PPM20	Project 20	Timothy,Stephen,	Somalia,Canada,Nauru,China
PPM21	Project 21	Victor,Harry,James	North Korea,Bahamas, The,Namibia,Indonesia,Montenegro
PPM22	Project 22	Precious,Harry,	Algeria,Nigeria,Kenya,Marshall Islands,Norway
PPM23	Project 23	Timothy,Jerry,James	Kuwait,Azerbaijan,Iceland,New Zealand,South Korea
PPM24	Project 24	Timothy,Jerry,	Pakistan,Thailand,Ghana,Sri Lanka,Iraq
PPM25	Project 25	Christian,Harry,	Mozambique,Djibouti,Slovenia,Korea, North,Tonga
PPM26	Project 26	Victor,Jerry,	Mauritius,Vietnam,Mozambique,Liberia,Romania
PPM27	Project 27	Victor,Harry,	Mali,Albania,Syria,Venezuela,Honduras
PPM28	Project 28	Christian,Harry,	Libya,Iceland,Hong Kong,Kazakhstan,Botswana
PPM29	Project 29	Victor,Yemi,	New Zealand,Colombia,Ethiopia,Zimbabwe
PPM30	Project 30	Christian,Harry,	Nepal,Bulgaria,Singapore,Aruba,Austria
PPM31	Project 31	Timothy,Stephen,Peter	Kazakhstan,Palestinian Territories,Tuvalu,Spain,Serbia
PPM32	Project 32	Karl,Jerry,	Singapore,Ecuador,Lesotho,Macau
PPM33	Project 33	BiJerry,Yemi,	Burkina Faso,Philippines,Vanuatu,Tuvalu,Tanzania
PPM34	Project 34	Karl,Harry,	Morocco,Korea,United Arab Emirates,Brazil
PPM35	Project 35	Christian,Stephen,Peter	Italy,Kuwait,Solomon Islands,Singapore,Cyprus
PPM36	Project 36	Timothy,Jerry,James	Turkey,Central African Republic,Brunei,Mali
PPM37	Project 37	Christian,Jerry,Peter	United Kingdom,Algeria,Antigua and Barbuda,Belgium,Saint Kitts and Nevis
PPM38	Project 38	Timothy,Stephen,	Micronesia,Serbia,Bulgaria,
PPM39	Project 39	Precious,Stephen,	Bhutan,East Timor (see Timor-Leste),Barbados,Albania,Afghanistan
PPM40	Project 40	Karl,Jerry,James	Djibouti,Spain,Mexico,Liechtenstein
PPM41	Project 41	Christian,Stephen,James	Central African Republic,Indonesia,Djibouti,Central African Republic,Belize
PPM42	Project 42	Precious,Yemi,Peter	Papua New Guinea,Uzbekistan,El Salvador,Ireland,Spain
PPM43	Project 43	BiJerry,Stephen,	Germany,Norway,Congo, Republic of the,Benin,Cambodia

Re: Dynamic split field into columns in script

marcus_sommer — Fri, 27 Jan 2017 14:46:06 GMT

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

Re: Dynamic split field into columns in script

hector_munoz — Fri, 27 Jan 2017 14:47:11 GMT

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:

And this is the model:

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

Regards,

Re: Dynamic split field into columns in script

ahaahaaha — Fri, 27 Jan 2017 14:59:23 GMT

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

Re: Dynamic split field into columns in script

Anonymous — Fri, 27 Jan 2017 16:44:43 GMT

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)

;

Re: Dynamic split field into columns in script

bigdataxhi — Mon, 30 Jan 2017 08:46:24 GMT

Hector, thanks.

Just what I needed!

Re: Dynamic split field into columns in script

hector_munoz — Mon, 30 Jan 2017 09:49:14 GMT

Glad to help you, 😉

Re: Dynamic split field into columns in script

vladimir_f — Tue, 31 Jan 2017 08:21:02 GMT

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