From VBS to QlikView

[Anonymous]

Hi guys,

               i've this VBS code:

sub selectWeight

     weight = ActiveDocument.Variables("vPeso").getcontent.string

     x = CDbl(weight)

     if x > 80 then

        ActiveDocument.Fields("CATEGORIE PESO").Select "Oltre gli 80 Q.li"

     else if  x > 60 then

               ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 80 Q.li"

          else if x > 50 then

                    ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 60 Q.li"

               else if x > 40 then

                         ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 50 Q.li"

                    else if x > 30 then

                             ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 40 Q.li"   

                         else if x > 25 then

                                 ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 30 Q.li"

                              else if x > 20 then

                                     ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 25 Q.li"

                                  else if x > 15 then

                                        ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 20 Q.li"

                                       else if x > 10 then

                                                 ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 15 Q.li"

                                             else

                                                  ActiveDocument.Fields("CATEGORIE PESO").Select "Fino a 10 Q.li"

                                              end if

                                     end if

                                end if

                             end if

                         end if

                     end if

                end if

             end if

         end if

end sub

is possible to translate in QlikView code?

Thank you

[Anonymous]

Sorry but maybe I didn't explain myself well.

As result I want only one row for each ID, and this row depends on the comparison between ROUND and TYPE.

For example:

weight = 12,25

suppose:

     ROUND_AA = 11

     ROUND_AB = 13

     ROUND_AC = 15

     etc

the result should be:

ID     TYPE               PRICE     ROUND     TOTAL

AA     less than 12     ...            11           .....               because ROUND_AA < 12

AB     less than 14     ....            13          ....               because ROUND_AB < 14

AC     less than 16     ...               15          ....               because ROUND_AC < 15

etc

I hope I explained it well

[marcus_sommer]

I don't understand the logic - why should Type be different for each ID (and if it should be rather an expression then a dimension or for each ID an own Type field) your ROUND_AA/AB/AC is not really a rounding to the weight of 12,25 - therefore what is the logic behind them and what is the aim?

- Marcus

[Anonymous]

Ok, maybe this example is clearer.

I would the lowest total, for every Seller.

For example:

for seller 2 the best choice could be type 2 A

but at the same time the best choice for seller 1 could be type 3 M

so the result should be

Seller 1 - type 3 - M - etc...

Seller 2 - type 2 - A - etc...

Seller 3 - etc...

this is only an example, the real app have more variables than this, and the total could be different every time.

I have a list of offers from many seller and I have choice the best for every one.

But the type could be different because every seller use different algorithm to categorize the product.

So I create a general system of classification that I can use, but however type could be different.

Now I don't know if pivot table is the best choice, but I need to show the information within a table and I have at the same time dimensions and expression.

At the end I want show only the best choice, not the other possibile choice.

I'm new in qlik, maybe there is another possibility to do this.

[marcus_sommer]

The aim is to generate a kind of dynamic pricing depending on various parameters, right? I think this a quite complicated task and you will need to combine a matrix of the fixed parameter (within one or several tables) and some nested queries on the flexible parameter (variables) for it. In your simplified example you used a few fixed values within the if-loops but this won't be work / be practically in a more complex case - therefore the table-matrix and for the expressions I suggest to use the pick(match()) - like in my example above.

Further I think you need two types of output-table - one with a result for each parameter-combination and one with a selected view on your wished best-case. But this best-case will be result of an expression and this couldn't be directly selected - this meant you need to compare the different results and find their max-value and to return NULL for all others and the NULL then could be hide and would therefore work like a selection.

That's now not a concrete solution (the task is too complex to develop one here) but that's the way I would start such a task and I would go it step by step untill I could define all requirements clearly and had concrete ideas how it could be designed - then I would start at beginning again.

- Marcus

[Anonymous]

Thanks for your answer.

I had this idea, I can create two table, the fist with all information

for example:

TALBE 1

seller1 - type1 - M - etc...

seller1 - type1 - A - etc...

seller1 - type2 - M - etc...

seller1 - type2 - A - etc...

seller2 - type1 - M - etc...

seller2 - type1 - A - etc...

seller2 - type2 - M - etc...

seller2 - type2 - A - etc...

seller3 - type1 - M - etc...

seller3 - type1 - A - etc...

seller3 - type2 - M - etc...

seller3 - type2 - A - etc...

etc...

now I read this table and I'll compose the second table with row from the first table

for example:

seller1 - type1 - A - etc...

seller2 - type2 - M - etc...

seller3 - type1 - M - etc...

etc...

can I do this?

[marcus_sommer]

I think the direction is right. At first the big matrix (as a kind of dimension table), the second step matched the fact-table with the dimension table and the third step will be the flexible calculations (on these fields and your variables) within the gui.

- Marcus