Select from excel

[#1]

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 .

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

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şcare	Material	Lot	Comandă
101	2000322	0000076418	1068402
101	2000322	0000076418	1068737
101	2000322	0000076418	1069134
101	2000322	0000076418	1069135
101	2000322	0000076418	1069135
101	2000322	0000076418	1069346
101	2000322	0000076418	1069346

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şcare	Material	Lot	Comandă
101	2000322	0000076418	1068402

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şcare	Material	Lot	Comandă
261	1000396	0000076418	1068402

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.

[#8]

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

[#9]

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

[#10 · Accepted solution]

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