Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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
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
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
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
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
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.
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