topic Re: Re: Load variable in QlikView

Load variable

Anonymous — Thu, 29 Aug 2013 18:36:13 GMT

Hi community,

I have many different files with the same questions, but different wording.

They are started from qID_2_blabla or qID_3_blabla.

I want, if any question started from qID_2_ save as AAA, if any question started from qID_3_ save as BBB.

Do you have any ideas how it easy to do?

Regards,

Re: Load variable

Anonymous — Thu, 29 Aug 2013 19:06:52 GMT

Vladimir,

You can use mapping. For example:

MyMap:
MAPPING LOAD * INLINE [
A, B
qID_2_, AAA
qID_3_, BBB
];

Data:
LOAD
...
applymap('MyMap', left(FieldName,6)) as NewField
...

RESIDENT ...

Regards,
Michael

Re: Load variable

kaushiknsolanki — Thu, 29 Aug 2013 19:11:40 GMT

Hi,

If they are the field values when you can simply write if statement.

Another approach you can use if of for loop.

If you know how much questions are there, then run a loop and check for the questions and bucket them accordinly.

Regards,

Kaushik Solanki

Re: Load variable

Anonymous — Thu, 29 Aug 2013 19:25:14 GMT

Hi Michael,

It won't be work, because some questions have more than 6 digits like it. qID_385_.

My idea to create universal load, where I only select which the file I want to load.

Thanks,

Re: Load variable

Anonymous — Thu, 29 Aug 2013 19:31:36 GMT

Hi,

I know only numbers without wording. Because writing is changed from time to time.

I'd like to create universal load, where I can work with constant variable.

Regards,

Re: Load variable

Anonymous — Thu, 29 Aug 2013 19:51:05 GMT

There is no logical difference... There is some technical difference - you need to find the position of the second '_', and cut all after it.

left(Field, index(Field,'_',2))

Now, you can use it as is, or convert it to something shorter using AutoNumberHash128:

AutoNumberHash128(left(Field, index(Field,'_',2))) as NewField

Re: Load variable

Anonymous — Thu, 29 Aug 2013 19:58:36 GMT

Thanks for reply,

I used both your ideas.

AutoNumberHash128(left(Field, index(Field,'_',2))) as NewField

Result 1 or 2

left(Field, index(Field,'_',2))

listbox is empty

Re: Load variable

Anonymous — Thu, 29 Aug 2013 20:07:56 GMT

It implies that there is no two '_' characters in field values... Can you upload an example?

Re: Re: Load variable

Anonymous — Fri, 30 Aug 2013 16:38:20 GMT

Hi Michael,

Please find the attached file.

Regards,

Vladimir

Re: Re: Load variable

Anonymous — Fri, 30 Aug 2013 16:40:10 GMT

Please also find xls file.

Thanks,

Re: Load variable

Anonymous — Fri, 30 Aug 2013 22:16:02 GMT

Now I see what the question is about. The " qID_2_blabla or qID_3_blabla" are not the values - they are filed names. So, all suggestions above are not applicable. Simple renaming is not acceptable for the simple reason that you cannot have multiple fields with the same name.

Here comes the question - what do you want to do with it? Possible options:

1. use only one field of similar named

2. use all of them and use the data of all these fields

3. anything else (?)

Regards,

Michael

Re: Load variable

Anonymous — Tue, 03 Sep 2013 14:32:35 GMT

Hi Michael,

I have many different files with the same name, but different wording.

I want to create universal load, where I select which file I want to load and all questions name change their name on for example AAA, BBB, and so on.

In charts and tables I can use variables AAA, BBB, and so on.

All questions names started from qID_NUMBER_TEXT. I want to recode the question, if it started from qID_2_ to AAA, if it started from qID_3_ to BBB, ...

Thanks,

Re: Re: Load variable

Anonymous — Tue, 03 Sep 2013 15:06:07 GMT

Hi Vladimir,

If you load only one file at a time, and if there are no "duplicates" within one file (your example has 227 twice, and it is a problem), there is a solution. In the attched example, I rename column names to Qnnn. So, when you load the excel file, you get Q150 instead of qID_150_<whatever>. If you load another file with qID_150_<something different>, you'll get the same Q150 instead.

The file to load is defined by a variable - see input box on top.

Hope it helps...

Regards,

Michael

Re: Load variable

Anonymous — Tue, 03 Sep 2013 15:16:07 GMT

For me more important wording than Q23 or O24.

I think it's difficult way.

I'll load by questions from SQL.

Re: Re: Load variable

Anonymous — Tue, 03 Sep 2013 15:28:34 GMT

Hi Vladimir,

I added mapping table and applymap in the script so it can convert Qnnn to the specific wording per map. As for hte load form SQL - it doesn't matter much. If you load from different tables that have same Q numbers with different wordings, you'll have to repeat this script for each table (except the map of course).

Regards,

Michael

Re: Re: Load variable

Anonymous — Tue, 03 Sep 2013 15:37:54 GMT

Thanks,

It's close to my question.

Thanks,