topic Re: Creating variables in QlikView

Creating variables

Thu, 21 Jun 2012 11:25:43 GMT

Hi Everyone,

I have loaded the data in qlik view from multiple excel sheets,one sheet has 25000 rows

code:

//part1

Employee:

load

EmpNo,

Name

Department

from Sheet1;

//part 2

Mapping:

load

EmpNo,

[Mapping]

from Sheet2;

part 3

left join (Sheet1)

load

EmpNo,

[Mapping]

if(isnull([Mapping]), 'Home',[Mapping]) as Map1

resident sheet2;

drop tables Mapping;

part4

left join(Employee)

load

EmpNo,

if(Map1 = 'XYZ' and Name = 'ABC', 'PQR',Map1) as Map2

resident Employee;

this query dosen't bring the data and this problem observed after adding part 4 in the code, I have to add atleast 15 such variables in the code.

please suggest the solution

I am new to coding, pls share if anyone has any docs on coding as well as best practices for coding.

Thanks in advance.

Re: Creating variables

chematos — Thu, 21 Jun 2012 13:33:49 GMT

First of all, I recommend you to rename Employee table to Employee_temp because in part4 you are going to create the table Employee with data of Employee_temp adding the last field:

if(Map1 = 'XYZ' and Name = 'ABC', 'PQR',Map1) as Map2

I think that you have to do this:

//part4

EMPLOYEE:

load

if(Map1 = 'XYZ' and Name = 'ABC', 'PQR',Map1) as Map2

resident Employee_temp;

You should use the wizard to bring data from your sheets, in the script section, the button below that is Files .... or something like that, I´m using the spanish version and is Ficheros Planos...

Re: Creating variables

Fri, 22 Jun 2012 05:31:27 GMT

Hi Jose,

Thanks for your reply.

I may require to use variable created in Employee table in the creation of other variables.

So, if I go with your logic then I may have to create separate tables for each variables..correct me if am wrong.

what does * do in your code? Does it load all the columns from Employee_Temp?

I have to achieve following output with following coloumns

EmpNo Name Department Mapping Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 Var10

Var2 needs to be created using Var1 and Department coloumn

Var3, Var4, Var5 --- same as var2

Var6 needs to be created using other Mapping table and the main employee table.

Var7.....Same as Var6

In My code Part 2 is nothing but the inclusion of Mapping Table, I have 6 same kind of Mapping tables..

In Part 3 of my code, Pls replace sheet1 with Employee table and sheet2 with Mapping table.

Please help me with the best approach to achieve this requirement as well as the what kind of performance tunning steps we need to keep in mind while writing the code.

To get the data in cronological order for variables, Do we have to always use the Left Join for creation of variable?

Thanks

Re: Creating variables

chematos — Fri, 22 Jun 2012 14:28:59 GMT

Hi,

Yes, * is to load all the fields from the table.

If I´ve understood well, may be there is a simple solution although I have not tried yet.

There is a way to use fields that they are not created yet that its called load preceding so you can calculate fields and use them in the same load without making another table and a left join, it´s better if I write an exmaple:

EMPLOYEE:

LOAD *,

If(Map2 = 'XXX' and Department = 'YYY', 'NEW_VALUE',Map2) as Map3,

if(Map3 = ..........................................................................) as Map4,

if(Map4 = ..........................................................................) as Map5,

....

;

load

if(Map1 = 'XYZ' and Name = 'ABC', 'PQR',Map1) as Map2

resident Employee_temp;

That is the syntax, the first load is executed after the second Load sentece, that´s why you can use the field Map2 in the first Load sentence.

I've used that many times but never with this particular case, so try it and tell me if it works.

Regards

Re: Creating variables

Fri, 22 Jun 2012 15:43:37 GMT

Thanx alot for your reply..i will test it and will update u.

As i mentioned in my second post that some of variables are comming from different mapping tables.

Currently i am loading all mapping tables and then creating variable and applying left join to get the variable in main table..

I think i am doing wrong...

Pls suggest me the suitable way to achieve this..

Pls tell me do we have to follow data modelling (star schema)while coding

If yes pls tell me how can i achive this in my scenario..

also while writing code i always try to bring all the variables and colums of mapping tables in the main table...

My understanding is that when we apply left join between main table and the mapping tables then obly i will get the data in expected order.

Does it right way to do the coding?

Thank

Re: Creating variables

chematos — Fri, 22 Jun 2012 15:50:58 GMT

I cant see this right now, but I assumed that you knew about applymap() function and mapping tables.

Are you using all of this?? If you don´t, you should start reading about applymap function.

Regards, I will take a look of that

Re: Creating variables

Sat, 13 Oct 2012 15:27:42 GMT

hi there,

I am with basic knowledge of qlikview, but less with coding part, so want to know how to cope up with this qlikview.

And also want to know that how to be a good developer in qlikview

Re: Creating variables

Anonymous — Sat, 13 Oct 2012 17:16:47 GMT

hi ss,

why you are creating someny variables in scripting,if you create a flag based on your condition in if statement.

can yoou provide test data i will show you.

Re: Creating variables

Sat, 13 Oct 2012 17:22:04 GMT

my question is , as I am new to qlikview and not having enough coding skills, how can I go ahead as qlikview developer to do better