Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Creating variables

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.

ss

8 Replies
chematos
Valued Contributor II

Re: Creating variables

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

Not applicable

Re: Creating variables

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

ss

chematos
Valued Contributor II

Re: Creating variables

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

Not applicable

Re: Creating variables

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

ss

chematos
Valued Contributor II

Re: Creating variables

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

Not applicable

Re: Creating variables

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

shaik_basha
Contributor III

Re: Creating variables

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.

Not applicable

Re: Creating variables

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

Community Browser