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:
LOAD [Tip miscare],
(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 *
[..\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
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.
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
TOT NECESAR Send.xlsx 1.7 MB
As per my understanding, I have done some changes.
You have to use same logic to go ahead.
Please find the attachment.
comm132875.qvw 433.2 K
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
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.
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.
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.
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
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.
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.
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.