8 Replies Latest reply: Oct 13, 2012 1:22 PM by Reshma Fatkare RSS

    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

        • Re: Creating variables
          Jose Tos

          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

              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

                • Re: Creating variables
                  Jose Tos

                  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

                      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

                • Re: Creating variables
                  khadar basha

                  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.