12 Replies Latest reply: Sep 11, 2014 1:36 PM by Angad Singh RSS

    Select  from excel

      New approach try to understand pieces of qlikview way fo working  so:

       

      Directory;

      LOAD [Tip mişcare],

           Material,

           Lot,

           Comandă

      FROM

      [TOT NECESAR.xlsx]

      (ooxml, embedded labels, table is Foaie1);

       

       

      ODBC CONNECT TO [Excel Files;DBQ=C:\Users\simion.alexandru\Desktop\Project\export segmentant\TOT NECESAR.xlsx];

      SQL SELECT *

      FROM `C:\Users\simion.alexandru\Desktop\Project\export segmentant\TOT NECESAR.xlsx`.`Foaie1$`;

      Create Tablenew(

      [Tip mişcare] int(3),

           Material nt(15),

           Lot int(15),

           Comandă int(15))

       

      Where Lot=x  and [Tip mişcare]=101

      Insert Into Tablenew 

      SELECT *

      FROM `C:\Users\simion.alexandru\Desktop\Project\export segmentant\TOT NECESAR.xlsx`.`Foaie1$`;

      Where Lot=x  and [Tip mişcare]=101

       

       

      Till here i hope i loaded the table and its a database table now and cna execute sql .

      Normaly i write Where Lot=x and Tip miscare=y but where is not lighting up so that not good and will not work but how to make it work

       

      I want to load data and make where lot=x and [Tip miscare]=101  (not sure why it take [] for tip miscare also ? anyway what to do like i didn t write how i shoudl  so where works?i didn t put the where in the right position?I shound use ODBC CONNECT TO... ( i used in the hope i could start using normal sql writing code that i know  but it doesn t do that i see ...

       

      So pleace baby steps as i still can cracked how the rules of writing script code are ,pls put code if possible to understand better so i can relate to how it is with how it would be in sql  so i can uderstand better.

      After i do the select i want the result to be stored and to be used for another where ?I tried in another post to ask with what i figured  with INTLINE and other stuff but the asnwers help a little at first and after had more question as code still not working so tryign this approach .

        • Re: Select  from excel
          Angad Singh

          Hello,

           

          Do you want to select a row of data from Excel and save it into a Database table using Qlikview ?

           

          This is what is expected?

           

          Thanks,

          Singh

            • Re: Select  from excel

              I don t have a database to connect to just and excel at this stage  ,later on after i make a function thing and complete will take data from a database.

               

              By importing excel i think it make a database table but considering all question i have regarding qlikview at this point i maybe wrong about this also.

               

              For example i right this code:

               

              Directory1:

              LOAD [Tip miscare],

                   Material,

                   Lot,

                   Comanda

              FROM

              [..\TOT NECESAR.xlsx]

              (ooxml, embedded labels, table is Foaie1)

              Where Lot=78184 and [Tip miscare]=101;

               

              I load excel file and made it liek this to give me the first result i need ,now i need to save it some how because based on it i will need to make another where using value comanda  offered in the up script result + tip miscare 261(where comanda =value from running up script and  tip miscare=261 .

              How to write and where in the code to write ?how to store /insert into a table that i create and how to do it ? the value from select and how to make the second stage select based on first select values.

               

              So continuign this script up how to write store  data  and select  *

              FROM

              [..\TOT NECESAR.xlsx] where comanda=value from running up script   for comanda column and tip miscare=261 and store that as i will have another where to make for the 5 result i shoudl get  for the second where

                • Re: Select  from excel
                  Angad Singh

                  Let us go step by step and correct me if i am wrong:

                   

                  1st) we filter from the excel file (Where Lot=78184 and [Tip miscare]=101). It will give me all rows respecting this criteria, say we get 100 rows.

                   

                  2nd) Now out of above 100 rows you need to again apply filter for field comanda. And [Tip miscare]=261?? Since first filter restricted all rows with [Tip miscare]=100, how can it contain 261 ?

                   

                  Can you please attach your excel file?

                   

                  I think we can surely find a solution, once things are more clear.

                   

                  Thanks,

                  Singh

                    • Re: Re: Select  from excel

                      Ok i put all i have to work with but hope you don t get lost in it.

                       

                      So in export i have a lot of column but what i need to make is like in the pics as a end result.

                      My main focus as column is in the 4 column tip miscare,material,lot,comanda for what i need now,the load excel in first post is practicly a excel with those 4 columns of interest only so its clear.

                       

                      Like in pic i start with a lot 78184 that is a final result and retrace the steps to how it was made /compose of .

                      You can see better the steps in ToT necesar send where i specified step for a complet run on a line of many till stop.

                       

                      For lot=78184 with tip miscare 101 i get 1 result with 4 column with my script.

                      Second where comanda=take from up result and  tip miscare=261  i will get 5 result  (you can see in excel or if you put filters in excel with condition from where.

                      For every one of the 5 result i put another where condition,for the first result of the 5 it will be   lot= 76418 and material =2000322 or 2000778 and tip miscare =101

                        • Re: Re: Re: Select  from excel
                          Angad Singh

                          Hello Alexandru,

                           

                          As per my understanding, I have done some changes.

                           

                          You have to use same logic to go ahead.

                           

                          Please find the attachment.

                           

                          Thanks,

                          Angad

                            • Re: Re: Re: Re: Select  from excel

                              thank you very much i am making a foot of progress with this  ,I  read the code  and i run it and its ok till 3 step there its a mystake  or something as its not possible to get that paramaters.(will try to correct it myself  but wanted to say) i would need other asists on the way if possible but in any case you give me the answer for what i needed to start and something i can related to understand and replicate.

                               

                              as in pick result in 3 step not possible as you don t use initial lot anymore ,you use from previous where result  in this case:

                              Lot
                              0000076418
                              0000077372
                              0000075590
                              0000077840
                              0000078258

                               

                              Now all 5 lot  up we take one till it end  after next one.

                              For 76418 after  3 where  i should get :                                      Where lot =76418 and material=2000322 and tip miscare =101

                              Tip mişcareMaterialLotComandă
                              101200032200000764181068402
                              101200032200000764181068737
                              101200032200000764181069134
                              101200032200000764181069135
                              101200032200000764181069135
                              101200032200000764181069346
                              101200032200000764181069346

                               

                              For 77372.....

                              till

                              For 78258

                              Finish 3 where clause run

                               

                              Now for the up table results we take  again one by one ,first one is this:

                              Tip mişcareMaterialLotComandă
                              101200032200000764181068402

                              Where tip miscare =261 and comanda = 1068402

                              and we get final answer for this branch and move to another branch.

                              Final   branch

                              4)  where comanda=1068402  and tip miscare =261
                              Tip mişcareMaterialLotComandă
                              261100039600000764181068402

                               

                               

                              I will try to correct that and make the other where  shoudl be hard now that i have the model ,also want to ask if what i am doing can be made in another way like a loop with cursor  in lesser code volume?if not to complicate ...

                              Also i see you make option in script for input initialised with 000000 for  lot ,can you help me by pointing me in the right direction  i would like to have 2 boxes tied so when i input in interface box lot and tip miscare for starting to get it for there and not go to script editor but not sure where to write and how  code to link it to the box object.

                               

                              Hope i was clear enought in what is wrong.

                                • Re: Select  from excel
                                  Angad Singh

                                  Great! it gave a start for the requirement.

                                   

                                  For the condition:

                                  Where lot =76418 and material=2000322 and tip miscare =101, I got zero records from the TOT excel file.

                                  May you know, better how to deal with it.

                                   

                                  You can loop as well concatenate command in the script.

                                   

                                  When we load 00007123 in qlikview, it by default removes the zeros from it. That is why in LOAD statement i used NUM() function to fix the format.

                                   

                                  The input box and other list box created in front end is just for my testing.

                                   

                                  And yes, things can be made more better, but I would recommend to first meet the final result. Once the final set of data is ready, then we can think of making it better and more dynamic as well.

                                   

                                  Thanks,

                                  Singh

                                    • Re: Re: Re: Re: Select  from excel

                                      Strange i repeat the filtering in excel with exact condition and i get  same as i put in tabel result , don t understand what you mean with The input box and other list box created in front end is just for my testing?Also added a write paper pic of schema.

                                      Also now as you started model i will make poses a problem later on as like in pic i need to show   in user interface :

                                      2 boxes input after result  that follow and here appear another question:

                                       

                                      like in pic i showed have structure like this see pic,i can not select some fields so they don t show but the extend have no claer idea how to make it .

                                      Also this way of doing will generated a lot of values ,we have like 12 now almost and we just cover one full start till finish branch so that why i ask if any way to make a loop or soemthign and how to implement and example a lot or a cursor  in qlik.

                                      Because if i define every time comanda3 till comand 200 my code will be huge and will take like forever.Hope you understand what i mean.I know is good to have initial values to see and after see how to maek efficient but not a lot of material covering and findind with ease so any ideas how a solution cursor/loop or other woudl look like in qlik as script format at least as this way to make what i need to have it not efficient as combination are to many and result to many values that need defining

                                        • Re: Select  from excel
                                          Angad Singh

                                          Surely, we will loop to make our code look better and dynamic. Qlikview gives us looping capabilities as well in the script.

                                           

                                          Moreover, you need to decide as per your requirement, whether you want to show the final result table to the Customer, on passing certain initial parameters. OR

                                           

                                          You will have charts and filters in Dashboard available, to let customer reach the final result.

                                           

                                          Since, now you have something to start with. I would ask you to play around with this and let the community know, wherever you get stuck.

                                           

                                          Hope this was helpful.

                                           

                                          Thanks,

                                          Singh

                                    • Re: Re: Select  from excel

                                      Also wanted to ask  how can this be done from click using object search as I heard its very much possible  and a lot faster then learning code difference from SQL oracle and writing in script .

                                      Appreciated any input as one  said this is double in 30 min  from interface of qlik mostly clicks for somebody that now the interface pretty well and have experience working with it.

                                        • Re: Select  from excel
                                          Angad Singh

                                          As I shared with you earlier, either we can make script changes to get final result or we can make user make selections to populate result.

                                           

                                          For the second case, we need to still define a data model in the script, to which user will play with at Front end.

                                           

                                          Suppose for your 1st filter:

                                          We can add listboxes of Lot and [Tip miscare] fields(listboxes have search facility as well)

                                           

                                          now, when user selects 0000078184 and 101 from listboxes, the charts and tables created will automatically reflect that as filters.

                                           

                                          But for this we need to a well connected data model behind.

                                           

                                          Thanks,

                                          Singh