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!
Solved! Go to Solution.
////Let's suppose this is your xls file:
LOAD * INLINE [
Consumer, Young, Adult, Aging
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
If(lower(Young)='x','Young', If(lower(Adult)='x','Adult','Aging')) as Age ////This is where all it happens
////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
Karin, Pls try this:
LOAD * INLINE [
Consumer, Young, Adult, Ageing
Q, x, ,
W, x, x,
E, , , x
R, , x,
T, , x, x
noconcatenate LOAD Consumer, Category, flag
RESIDENT [temp] WHERE flag = 'x';
DROP field flag;
DROP table temp;
Hope this helps.
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.
Tried the following in my load script using your excel. Hope this is what you are looking to achieve.
CROSSTABLE(Concept_Category, Flag, 2)
LOAD [Project Name],
[On The Go],
FROM "8004.ConceptDatabase.xls" (biff, header is line, embedded labels, table is ConceptInfo$)
WHERE [Project Name] = 'Shapes' AND [Concept Name] <> '';
DROP field Flag;
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:
[Word 1] as Word
[Word 2] as Word
[Word 3] as Word
File & '-' & Word as JoinKey,
DROP TABLE WORDS_1;
left join (WORDS_2)
LOAD File & '-' & [Word to translate] as JoinKey,
//[Word to translate] as Word
RESIDENT TRANSLATE ;
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
Unfortunatelly I can't send you the whole application while it's very confidential...
Thanks all for your help. I solved it like this:
if(Young='x', 'Young') as Consumer
if(Adult='x', 'Adult') as Consumer
if(Aging='x','Aging') as Consumer
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.