Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

garethct
Contributor

Merge fields

Hi all,

I have an excel file that I'm loading into QV. There is a number of fields that have the same data and I am wanting to group these fields into one. Each row entry has a unique field.

Example.

Motor Topic
Home Topic
Pet Topic
Travel Topic
Amend
Sale
Sale
Renew

Aim.

Topic
Amend
Sale
Renew
Sale

Any help would be much appreciated.

Thanks a lot

gwassenaar

hic

stevedark

rwunderlich

jimhalpert

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Merge fields

Hi Gareth,

Simply load from it four times;

LOAD

    OtherFields,

     'Motor' as [Topic Type],

    [Motor Topic] as Topic

FROM ExcelFile.xlsx;

LOAD

    OtherFields,

     'Home' as [Topic Type],

    [HomeTopic] as Topic

FROM ExcelFile.xlsx;

LOAD

    OtherFields,

     'Pet' as [Topic Type],

    [Pet Topic] as Topic

FROM ExcelFile.xlsx;

LOAD

    OtherFields,

     'Travel' as [Topic Type],

    [Travel Topic] as Topic

FROM ExcelFile.xlsx;

Make sure the field list is identical each time and auto-concatenation will take place.

You could get cleverer with it, and load the list of fields into a table and enumerate around them.

A CROSSTABLE load will also do it for you, but if it is just four fields the way above may be cleaner.


The code for CROSSTABLE is:

CROSSTABLE ([Topic Type], Topic, 3)

LOAD
    *

FROM ExcelFile.xlsx;

The number 3 relates to how many fields there are in the table left of the first topic field.  It relies on all other columns in the spreadsheet being to the left of the topic columns.  You can force this by listing the fields in a certain order, rather than using a * (but a * allows for new topics without changing the script).

Hope that helps.


Steve

6 Replies

Re: Merge fields

I would probably use a Crosstable Load.

The Crosstable Load

HIC

Chanty4u
Esteemed Contributor III

Re: Merge fields

try this

a:

LOAD [Motor Topic]& ''& [Home Topic] &''&[Pet Topic] & ''& [Travel Topic] as Topic

FROM

(ooxml, embedded labels, table is Sheet1);

Chanty4u
Esteemed Contributor III

Re: Merge fields

tested with conc.PNG   because in list box you will get unique values thats why sales come once.

MVP & Luminary
MVP & Luminary

Re: Merge fields

Hi Gareth,

Simply load from it four times;

LOAD

    OtherFields,

     'Motor' as [Topic Type],

    [Motor Topic] as Topic

FROM ExcelFile.xlsx;

LOAD

    OtherFields,

     'Home' as [Topic Type],

    [HomeTopic] as Topic

FROM ExcelFile.xlsx;

LOAD

    OtherFields,

     'Pet' as [Topic Type],

    [Pet Topic] as Topic

FROM ExcelFile.xlsx;

LOAD

    OtherFields,

     'Travel' as [Topic Type],

    [Travel Topic] as Topic

FROM ExcelFile.xlsx;

Make sure the field list is identical each time and auto-concatenation will take place.

You could get cleverer with it, and load the list of fields into a table and enumerate around them.

A CROSSTABLE load will also do it for you, but if it is just four fields the way above may be cleaner.


The code for CROSSTABLE is:

CROSSTABLE ([Topic Type], Topic, 3)

LOAD
    *

FROM ExcelFile.xlsx;

The number 3 relates to how many fields there are in the table left of the first topic field.  It relies on all other columns in the spreadsheet being to the left of the topic columns.  You can force this by listing the fields in a certain order, rather than using a * (but a * allows for new topics without changing the script).

Hope that helps.


Steve

amit_saini
Honored Contributor III

Re: Merge fields

Like this ???
PFA!

garethct
Contributor

Re: Merge fields

Thanks everyone, Steve Dark's suggested worked!