Categorize Data with Transformer 1

[Julia23]

Hallo,

I have a little Problem. I must categorize this Data in levels:
2345_BMW_ozg
2344_BMW_iko

But how can I create a default script that categorizes this data in a BMW level? There are 3 more levels and this level is in the middle of this category. Can I use a sql script as "like "%BMW%"? and where can I input this? I have create manually a BMW Level in the Diagram View but if the database is updatet the new Data wouldnt categorize. Please help me and sorry for my poor English...

 

[blom0344]

Use a custom view on the table-data.
That way you will have a consistent definition on your source-data regardless whether data actually changes with the tables beneath.

Ties Blom

 

[CognosChicago]

Julia,

Are you trying to create a level above this information in a dimension in transformer?

 

[Julia23]

Thanks blom0344 for your Help,

I have already created a View. But I dont know how can I Filter the data with a like "%BMW%" order. In Access or SQL we can with Querys categorize the data in base of a Array. Here in Transformer how can I create such a level? Or is that not possible?

Thanks for your help again!!!

 

[Julia23]

Hallo CognosChicago,

yes I am trying to create a level above this information in a dimension in transformer. I have already created a New Dimension and then a new BMW level in the Diagram view. I have import the data and it works. My Question is if I can create a New level that categorizes the Products automatictly. I mean is that possible to insert a Script or SQL Script in the properties of the Dimension or Level that makes this job? Or is that posibble just in the Diagram View (I mean to crate a new Level that categorizes the BMW Products)...

 

[CognosChicago]

Julia,

You most certainly can. In your query create a new column which will become the level. For instance, you want to have BMW be the category for the new level. Create a field in the query which will result in the data being BMW, then use this new field as your level. The field can contain anything. I do this a lot when dealing with names of people. I create a field from the first letter of the last name and use this as a level in the dimension. I use a substring to get the first letter, so you can do something similar.

 

[Julia23]

Thank you CognosChicago,

its really a great Idea. I will make that.

thanks a lot again You are REALLY great!!!!

 

[CognosChicago]

Julia,

I have a rule of thumb when dealing with and creating PowerPlay Cubes. Always make the data do the work. What I mean by this is always, let the database and the query to the work for you. Do as little as possible in the Transformer Model as you can. It is all about preparing your data for the cube build. If you can do something easier in a query, then I say do it in the query and pass it along to Transformer.

Good Luck!

 

[Julia23]

Thanks again CognosChicago,

you are already an expert in this and any information is really helpfull for someone newbie like me. Thanks again and I will try my best!!!!

Julia

 

[Julia23]

Halo Cognos Chicago again!

Sorry to bother you but I am a bit confused and helpless. Can you maybe help me again?
I have this data:
59012-imo Markt Gesmbh
59023-Praktiker
59089-Praktiker
59067-Praktiker Hagen
60789-BMW
etc.

The first Level that I have are the Businnes Levels (they are in each column: Here the SQL Script first:
"CUSNO" as c1,
T1."CUSNM" as c2,
T1."CUSCL" as c3,
T1."BFADR6" as c4,
T2."I9ADR7" as c5,
T1."SLSNO" as c6,
T3."SLSNM" as c7,
T1."TERRN" as c8,
T4."DZCTTX" as c9,
T1."ENPCD" as c10,
T5."BNC4TX" as c11,
T6."BGCZTX" as c12,
T7."ALCOCD" as c13,
T8."BBCJTX" as c14,
T7."ALCPTX" as c15,
T7."ALBKCD" as c16,
(curdate()) as c17
from ((((((("ABC620"."AMFLIBX"."MBBFREP" T1 left outer join "ABC620"."AMFLIBX"."MBI9REP" T2 on T1."BFADR6" = T2."I9ADR6") left outer join "ABC620"."AMFLIBX"."SLSMAS" T3 on T1."SLSNO" = T3."SLSNO") left outer join "ABC620"."AMFLIBX"."MBDZREP" T4 on T1."TERRN" = T4."DZC0CD") left outer join "ABC620"."AMFLIBX"."MBBNREP" T5 on T1."ENPCD" = T5."BNBICD") left outer join "ABC620"."AMFLIBX"."MBBGREP" T6 on T1."CUSCL" = T6."BGC7CD") left outer join "ABC620"."AMFLIBX"."MBALREP" T7 on ((((T1."COMNO" = T7."ALAENB") and (T1."CUSNO" = T7."ALCANB")) and (T7."ALCUCD" = 1)) and (T7."ALE2ST" = '1')) and (T7."ALAGVN" <> '')) left outer join "ABC620"."AMFLIBX"."MBBBREP" T8 on T7."ALCOCD" = T8."BBCOCD")


So we have at the first Level TERRN and then CUSNO+CUSNM (this are added columns). Between TERNN and CUSNO+CUSNM I must insert a level. I want to categorize the other data too (as example Praktiker, BMW etc Levels) with only the Praktiker or BMW description. I have understand the theory but I cant do that so easy. I know how I can add a new column but then? And if I add the new Level than it would be just one new Categorie. I don´t want to categorize all Data. A few of them wouldn´t have a category... Sorry to bother you with that but I want to know how I can do that. It would be really helpfull for the Future and you know so much about Cognos. Can you show me what I must add to this SQL Script for do that? Sorry and thanks

 

[CognosChicago]

You will probably need to create a category for all data in the new level. You can have the two you need, Praktiker and BMW and then an "Other" or "No Category", or what ever you want to call it. So you would have an If Then Else statement in your SQL or a Case Statement.

If (substring(yourfield, start position, end position) = 'BMW') then ('BMW') else if (substring(yourfield, start position, end position) = 'Praktiker') then ('Praktiker') else ('No Category')

This is just an example. You will have to figure out the correct way for your data to pull out BMW or Praktiker. You need to setup the right business rule for this, but once you've done this, the new field you created will be the middle level.

Hope that helps.

 

[Julia23]

Thanks CognosChicago!

It's really helpfull! Yet I know how I can create this categories. I was a little confused with the substring command. Yet I know how I can use it. I have tried this and I have explore many others possibilities. I copy that command from you and work with this in the Future. It will be very helpfull!
Thanks a lot again!!!!!!!