Combine several fields into one

[#1]

Hi.

I'm quite new with QlikView and need help with something that probably is very easy, it's just that I don't know where to start.

I have a number of fields in my excel sheet where you insert a "x" in different columns in order to tell if your consumers are: Young and/or Adult and/or Aging. Right now these are in there different fields and I can not make a list box to be able to search for all the Young consumers. How can I do to combine these three fields into one?

Anyone that answers to my simple question: THANK YOU!

Karin

[#2 · Accepted solution]

Hello Karin,

please provide a short excel example we ca play around with.

Thank you!

Rainer

[#3]

////Let's suppose this is your xls file:
AgeTemp:
LOAD * INLINE [
Consumer, Young, Adult, Aging
Q, x
W, , x
E, , , x
R, , x
T, , x
];


////After that, you create a new table with a conditional field based on the information provided in your original mark fields
Age:
LOAD
Consumer,
If(lower(Young)='x','Young', If(lower(Adult)='x','Adult','Aging')) as Age ////This is where all it happens
RESIDENT AgeTemp;


////Finally you can drop your original table
Drop Table AgeTemp;

////the script is a little bit more complicated if you have consumers that are both young and adult in the same time

[#4]

Hi,

Check the attachment.

if the attached application doesnt solve your purpose.

Post your sample data.

- Sridhar

[amit_shetty78]

Karin, Pls try this:

AgeTemp:
LOAD * INLINE [
Consumer, Young, Adult, Ageing
Q, x, ,
W, x, x,
E, , , x
R, , x,
T, , x, x
];

[temp]:
CROSSTABLE(Category, flag)
LOAD Consumer,
Young,
Adult,
Ageing
RESIDENT AgeTemp;

[Consumer]:
noconcatenate LOAD Consumer, Category, flag
RESIDENT [temp] WHERE flag = 'x';

DROP field flag;
DROP table temp;

Hope this helps.

-Amit.

[#6]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8004.ConceptDatabase.xls:550:0]

Hi Rainer,

Here is a part of my excel file. Unfortunatelly I will have to insert my data in this way.

One thing that came to my mind was to load columns regarding age group seperate from the rest of the data and transpose the data. I will try and see what happens.

Thank you very much for helping me.

Karin

[amit_shetty78]

Karin,

Tried the following in my load script using your excel. Hope this is what you are looking to achieve.

Directory;
CROSSTABLE(Concept_Category, Flag, 2)
LOAD [Project Name],
[Concept Name],
[Micro portion],
[Portion pack],
[Family pack],
[Large size],
Young,
Adult,
Aging,
[On The Go],
[In Home],
WM,
OLDP,
JN,
SD,
[F&W]
FROM "8004[1][1].ConceptDatabase.xls" (biff, header is line, embedded labels, table is ConceptInfo$)
WHERE [Project Name] = 'Shapes' AND [Concept Name] <> '';

DROP field Flag;

- Amit.

[#8]

Hi all,

I have now tired all your suggestions and I realize that I have to clearify what I need. What I want to do is to make one field called Consumer, containing, Young, Adult and Aging. Another field called Occasion containing [On the Go] and [In Home], etc.

The closes solution was the one suggested by Sridhar, I have also find this similar solution in anothe application that we have where we need to put three fields into one. Unfortunatelly this didn't work all the way but hopefully I can do it looking at the other application we have. In our other application we want to make one field out of three fields all containing one keyword, it looks like this:

WORDS_1:

LOAD %Key_Word_Data,

File,

[Word 1] as Word

RESIDENT RAWDATA;

LOAD %Key_Word_Data,

File,

[Word 2] as Word

RESIDENT RAWDATA;

LOAD %Key_Word_Data,

File,

[Word 3] as Word

RESIDENT RAWDATA;

WORDS_2:

LOAD %Key_Word_Data,

File & '-' & Word as JoinKey,

Word

RESIDENT WORDS_1;

DROP TABLE WORDS_1;

left join (WORDS_2)

LOAD File & '-' & [Word to translate] as JoinKey,

Score,

[English word]

//[Word to translate] as Word

RESIDENT TRANSLATE ;

WORDS:

LOAD %Key_Word_Data,

Score,

[English word],

Word

RESIDENT WORDS_2;

DROP TABLE WORDS_2;

DROP TABLE TRANSLATE;

For each project we have one excel file so it makes it a bit more complicated. filename() '-' recno() as %Key_Word_Data

I don't know if this can help you. As I wrote before, I'm very green at this and often use copy-paste from other applications, I do not always really know what I am doing.

Thanks all for your great help

Karin

Unfortunatelly I can't send you the whole application while it's very confidential...

[#9]

Thanks all for your help. I solved it like this:

CONSUMER_1:

LOAD %Key_Data,

File,

if(Young='x', 'Young') as Consumer

RESIDENT ConceptInfo;

LOAD %Key_Data,

File,

if(Adult='x', 'Adult') as Consumer

RESIDENT ConceptInfo;

LOAD %Key_Data,

File,

if(Aging='x','Aging') as Consumer

RESIDENT ConceptInfo;

CONSUMER:

LOAD %Key_Data,

Consumer

RESIDENT CONSUMER_1;

DROP TABLE CONSUMER_1;

Thank you all. I'll come back soon with another problem because I realized that this is the best place to get answers. Before I have been looking in the manuals but it takes too much time.

Have a nice weekend.

Karin