Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
How do want to make Any in Combi?Explain a little?
You should use generic keys to do this. See more in the Tech Brief on Generic Keys.
HIC
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
If it helps here's a test app and data that I have set up
Best
Alexis
Check the file,this might something you want?
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
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
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
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