% calculation on yes/no fields in the load script

[dia2021]

I am trying to get the desired result based on yes/no fields in the load script. I want to capture the aggregated percentage for the summary view based on all  'yes' columns data elements. 

Please see attached file with the data end result. I need to create two tables, one with the summary view and another with a detailed view. 

I need help as soon as possible. I really appreciate any help you can provide.

Here is the script that I have created, but it's not working at the detailed level view. I need to create a pivot table with indent rows where numbers are repeating at the 2nd dimension drill. 

[Table]:
LOAD
[Order],
[Product],
[Metric],
[D],
[E],
[F],
[G,
[H],
[I],
[J],
[K],
[L],

FROM .....

NoConcatenate LOAD
"Order","Product", Count(DISTINCT Metric) as "# of Metrics"
Resident [Table] Group By "Order","Product";

Calc:
Load
"Order",
count(if(match([D],'Yes'),[D]))
+ count(if(match([E],'Yes'),[E]))
+ count(if(match([F],'Yes'),[F))
+ count(if(match([G],'Yes'),[G]))
+ count(if(match([H],'Yes'),[H]))
+ count(if(match([I],'Yes'),[I]))
+ count(if(match([J],'Yes'),[J]))
+ count(if(match([K],'Yes'),[K]))
+ count(if(match([L],'Yes'),[L])) As MDefYes,

count([D])
+Count([E])
+Count([F])
+Count([G])
+Count([H])
+Count([I])
+Count([J])
+Count([K)
+Count([L]) As MDefTotal

FROM where order = 'ID1'
Group By "Order";

Test:
Join Load "Order",
num((Sum(MDefTotal)/Sum(MDefYes))/100, '#,##0.00%' ) As R3Avg
RESIDENT Calc Group By "Order";

[rubenmarin1 · Accepted solution]

Hi I don't know wich table you call the 'Detail ' table but I see some errors and some typos in your code.

- You are checking Yes and the excel stores yes (without the initial uppercase)

-  count(if(match([F],'Yes'),[F)) ->  count(if(match([F],'Yes'),[F]))

- +Count([K) -> +Count([K])

- FROM where order = 'ID1' -> Resident Table (without the Where)

- Sum(MDefTotal)/Sum(MDefYes) -> Sum(MDefYes)/Sum(MDefTotal)

If you need the values by product just add another table that groups buy product or by order and product.

I fyou found another issue post the value you get and the expected result.

[dia2021]

Hi Rubernmarin, Thanks for your reply! 

Thanks for your reply Rubernmarin!

I have not added the Detail level table in the script. I will add and check the numbers by product. Also, make changes to the script as you suggested above. 

Sorry, - 'Yes' is in the initial uppercase in the real dataset. (I just created this file in a rush), but thanks for catching up. I tried creating the values by-product in another table that groups by order and product, it results in creating a synthetic key.  It's not taking group by syntax with both fields together.  I also tried the concatenate load after the load statement to solve the synthetic keys without any luck. Any idea?

I have a question can it be done through the loop statement because it is a huge dataset?

[dia2021]

Hey Rubernmarin!

It worked!! Thanks a lot!!  🙂

I created another table with the group by Product only and fixed the things you mentioned above.

Can I achieve this with the loop statement? Which would be the best option to implement this?

[rubenmarin1]

Hi, the synthetc key is between the original tabla and the grouped one? You can create a composite key to avoid it.

LOAD Order&'_'&Product as IdOrderProduct,

This composite key has to be loaded in both table to relate data, but Order and Product fields should be in only one table.

Why you want a loop? Usually a loop is slower than process all data.

[dia2021]

I have another question about the transpose table. I want to load it without affecting the main table. it is working but the problem is it's transposing the data elements associated with the main table. How can I load it separately?

I loaded all the fields that are present in the main table (Table1) in a separate table (Table2) with NOConcatenate Load then applied the Crosstable function with Resident (Table1) and drop table  (Table1) 

[rubenmarin1]

Hi, I missed this last question, is still an issue? I don't fully understand.. can you write why are you trying to do adding some little sample data to help to undertand it?