topic Re: Ignore null values in dimensions in App Development

Ignore null values in dimensions

TinaH — Tue, 15 Feb 2022 12:50:06 GMT

Hello together,

I am currently having a problem using multiple dimensions with many null vlaues. Suppose i have the following dimensions / values:

Role	2022-07	2022-08
2nd Level Support	Employee 1	-
2nd Level Support	Employee 2	-
2nd Level Support	-	Employee 3
2nd Level Support	-	Employee 4
Systemupdate - Code	Employee 5	-
Systemupdate - Code	-	Employee 6
Systemupdate - SQL	Employee 7	-
Systemupdate - SQL	-	Employee 8

But my goal is that the table looks as follows:

Role	2022-07	2022-08
2nd Level Support	Employee 1	Employee 3
2nd Level Support	Employee 2	Employee 4
Systemupdate - Code	Employee 5	Employee 6
Systemupdate - SQL	Employee 7	Employee 8

How can i achieve this?

Thank you!!

Best regards
Tina

Re: Ignore null values in dimensions

chris_djih — Tue, 15 Feb 2022 12:58:41 GMT

For me it looks like your challenge is not ignoring null values but get a better matching of the data model.

Can you provide more details on your example tables: is this your raw data or is this already a result of scriptung?
What is the goal of this table? Is this a kind of employee schedule?

Re: Ignore null values in dimensions

TinaH — Tue, 15 Feb 2022 13:26:50 GMT

Yes, the objective of the app should be an employee schedule.

The basis for this is an Excel table. At the top you can find the respective calendar weeks as numbers and then the assignment of the employees to the roles per calendar week. Currently, I read the Excel file twice in succession, always for the respective week, i.e.:

let vWeek = '['&week(today())&']'; let vYear = '['&year(today())&']'; [MITARBEITER]: LOAD [F2] AS Emplyee, '1' AS Kontext, Pick ( match ( $(vWeek) , 'a' , 'f' , 'c' , 's' , 'r' ) , 'abwesend' , '2nd Level Support' , 'Systemupdate - Code' , 'Systemupdate - SQL' , 'Release Manager' ) AS Role FROM [lib://AttachedFiles/Schedule.xlsx] (ooxml, embedded labels, table is $(vYear))

And the same code for the following week, but '2' AS Kontext.

My objective is to display the table as described in my question above. Does this help?

Re: Ignore null values in dimensions

chris_djih — Tue, 15 Feb 2022 14:13:13 GMT

"And the same code for the following week" - and here lays the interesting point.
I assume you load it exactly the same wiht out any join or something like this?
Then it will automatically concatenate (append the table):
If you want to combine the lines, i think you need to join them.

Re: Ignore null values in dimensions

TinaH — Tue, 15 Feb 2022 14:24:49 GMT

Yes exactly: I load the data for the following week the same way, without using any join.

However, when I join the tables, I get the same result as above. Would it help if I publish an example file?

Re: Ignore null values in dimensions

chris_djih — Tue, 15 Feb 2022 14:44:54 GMT

Yes, that would be very helpful. please give an example of the rawdata as well. so the community can puzzle around to get to the correct result.

Re: Ignore null values in dimensions

TinaH — Tue, 15 Feb 2022 14:49:01 GMT

Please find attached the example file as well as an example of raw data.

Thank you very much!

Re: Ignore null values in dimensions

TinaH — Wed, 16 Feb 2022 11:34:07 GMT

Someone any ideas?