topic Re: Select from excel in QlikView

Select from excel

Thu, 11 Sep 2014 06:17:23 GMT

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

Thu, 11 Sep 2014 06:37:34 GMT

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

Thu, 11 Sep 2014 07:17:16 GMT

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

Thu, 11 Sep 2014 07:29:52 GMT

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

Thu, 11 Sep 2014 07:47:18 GMT

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

Thu, 11 Sep 2014 09:05:14 GMT

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

Thu, 11 Sep 2014 10:00:31 GMT

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.

Re: Select from excel

Thu, 11 Sep 2014 10:24:55 GMT

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

Thu, 11 Sep 2014 11:02:13 GMT

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

Thu, 11 Sep 2014 11:40:19 GMT

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: Re: Re: Select from excel

Thu, 11 Sep 2014 12:05:06 GMT

Ok thanks again now trying to find ways to write the 3 where clause with loop and other way .

I see a match function and the loop

Re: Re: Select from excel

Thu, 11 Sep 2014 17:15:57 GMT

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

Thu, 11 Sep 2014 17:36:20 GMT

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