Several fields to one

[#1]

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:\.....

[sunny_talwar]

Only one of those fields will ever be 1?

[MarcoWedel]

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

[PrashantSangle]

Hi,

Explain little bit more . Hard to understand your requirement.

Regards

[#5]

See attached excel.

[#6]

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

[#7]

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.

[settu_periasamy · Accepted solution]

May be like attached?

[MarcoWedel]

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