Subfield command for "Any" (wildcard)

[alexis]

I wish to expand the Combis table to replace the delimited "Tec" and "Class" fields into individual rows.

Most of it can be done REALLY easily by saying (in the Combi load):

subfield (Tec, '|') as TecID          // this will join it with the Tec table

subfield (Class, '|') as ClassID    // this will join it with the Classes table

Any ideas how to expand/handle the Any (in Tec) and Any (in Class) ?

Thanks in advance

Alexis

[sivarajs]

How do want to make Any in Combi?Explain a little?

[hic]

You should use generic keys to do this. See more in the Tech Brief on Generic Keys.

HIC

[alexis]

Hi Sivaraj,

Thank you for replying.

When we we see "Any" in the "Tec" column for example as we do in:

Owner     Tec     Class         WorksheetName

104          Any     1|2               Sheet_4

we need to expand it for all possible combinations remembering that "Any" in Tec means that it can take values of 1,2 through to 6, and Class can take the values 1 or 2

Owner     Tec     Class         WorksheetName

104          1          1               Sheet_4

104          2          1               Sheet_4

104          3          1               Sheet_4

104          4          1               Sheet_4

104          5          1               Sheet_4

104          6          1               Sheet_4

104          1          2               Sheet_4

104          2          2               Sheet_4

104          3          2               Sheet_4

104          4          2               Sheet_4

104          5          2               Sheet_4

104          6          2               Sheet_4

Also bear in mind that both Tec and Classes as you see them in the above diagram have been setup as tables so are accessible to our application.

Hope this helps

[alexis]

If it helps here's a test app and data that I have set up

Best

Alexis

[sivarajs]

Check the file,this might something you want?

[jonathandienst]

Hi

I am not sure if you want this to happen in the load script or in the front end - the attached model constructs an expanded combi table in script.

Hope that helps

Jonathan

[alexis]

Hi Sivaraj,

Thank you for your response.

I like your simplistic approach to the problem. Unfortunately there are 3 shortcomings with your solution:

a) You have hard-coded the combinations of "Tec" and "Class" (see Any1 and Any2 in your code) - what if there is a new Tec added for example?

b) You have assumed that there are the same combinations of "Class" (values 1-6) as there are for Tec. Values for "Class" are 0,1,2,3

c) You solve the problem by creating a Calculated Dimension. My requirement is to have all the results in my Combis table in the application.

Taking your code I have made some modifications so that it removes all the hard-coding and uses the actual combinations of Tec and Class to "almost" solve the problem - see attached application. All that remains now is to record the combinations in the Combi table inside the application rather than rely on a "Calculated Dimension" - you will see my attempt to achieve this in the code (commented out).

Let me know if you have ideas of how to solve that.

Thanks again

Alexis

[alexis]

Hi Jonathan

The results in your solution are 100% correct and you are solving the question in the load script which is exactly what I wanted. Your code is also a great tutorial for JOINs!!

I have not nominated your solution as the "Correct answer" bacause I wonder if there is a simpler way to achieve the result perhaps by using your solution and my amended one (see Combis3.qvw above) based on Sivaraj's original suggestion.

In Comis3 we are solving the problem in the front end. The requirement is to solve it in the Load script.

Thank you once again for the time that you have spent helping me out.

Alexis

[sivarajs · Accepted solution]

Hi,  

Q a & b : I have just taken a example to show how this can be implemented

               Now it should work if anything gets added to Tec table

Q c: I have just modified your expression in script.No calculated dimension

Check then let me know if anything wrong