topic Re: Several fields to one in QlikView

Several fields to one

Tue, 05 Apr 2016 16:21:40 GMT

Hi community,

I have a table with several of fields. Some of them I would like to group to one field and then use it as av filter. I have tried with below:

LOAD ID,

HO,

HOKO,

NA,

VA,

RI,

HY,

HYSK,

HYMO,

if(HO = '1', 'HO',

if(HOKO = '1', 'HOKO',

if(NA = '1', 'NA',

if(VA = '1', 'VA',

if(RI = '1', 'RI',

if(HY = '1', 'HY',

if(HYSK = '1', 'HYSK',

if(HYMO = '1', 'HYMO',

if(HP = '1', 'HP '))))))))) as GROUP_FILTER,

FROM

D:\.....

Re: Several fields to one

sunny_talwar — Tue, 05 Apr 2016 16:26:08 GMT

Only one of those fields will ever be 1?

Re: Several fields to one

MarcoWedel — Tue, 05 Apr 2016 23:44:48 GMT

I guess you could use a crosstable load to generate your Group_Filter field.

Please provide some sample data and expected result to demonstrate.

regards

Marco

Re: Several fields to one

PrashantSangle — Wed, 06 Apr 2016 05:36:09 GMT

Hi,

Explain little bit more . Hard to understand your requirement.

Regards

Re: Several fields to one

Wed, 06 Apr 2016 05:47:23 GMT

See attached excel.

Re: Several fields to one

Wed, 06 Apr 2016 05:50:30 GMT

Yes, thats right. So do you have any suggestion have to solve this?

Re: Several fields to one

Wed, 06 Apr 2016 05:54:08 GMT

I would like to combine several field from an excel. If the field have value 1 I would like to use it in a list box as a filter.

See attached excel for sample data.

Re: Several fields to one

settu_periasamy — Wed, 06 Apr 2016 06:33:43 GMT

May be like attached?

Re: Several fields to one

MarcoWedel — Wed, 06 Apr 2016 22:41:13 GMT

Hi,

one solution to keep all source fields like your question suggests and using your unique ID field could be:

table1:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1013299-219878/jpgroup%20-%20Copy.xlsx] (ooxml, embedded labels, table is Blad1);

tabGrpFilt:
CrossTable (GROUP_FILTER,GROUP_FILTER_VALUE,4) LOAD * Resident table1;
Right Join
LOAD Distinct GROUP_FILTER_VALUE Resident tabGrpFilt Where GROUP_FILTER_VALUE;

DROP Field GROUP_FILTER_VALUE;
DROP Fields JPNUM, GROUPID, TYPEID From tabGrpFilt;

hope this helps

regards

Marco