beeye - no facts?! - Qlik, Jedox und Softwareentwicklung

Excel macro loop by sheet

15/03/2019

Collect data from multiple excel tabs and put them together at a new collector sheet:

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MA_NAME = ActiveWorkbook.Worksheets(I).Name

vJanuar = ActiveWorkbook.Worksheets(I).Cells(8, 3)
vFebruar = ActiveWorkbook.Worksheets(I).Cells(8, 4)
vMarch = ActiveWorkbook.Worksheets(I).Cells(8, 5)

vzJanuar = ActiveWorkbook.Worksheets(I).Cells(9, 3)
vzFebruar = ActiveWorkbook.Worksheets(I).Cells(9, 4)
vzMarch = ActiveWorkbook.Worksheets(I).Cells(9, 5)

ActiveWorkbook.Worksheets(239).Cells(I, 1) = MA_NAME

ActiveWorkbook.Worksheets(239).Cells(I, 3) = vJanuar
ActiveWorkbook.Worksheets(239).Cells(I, 4) = vFebruar
ActiveWorkbook.Worksheets(239).Cells(I, 5) = vMarch

ActiveWorkbook.Worksheets(239).Cells(I, 7) = vzJanuar
ActiveWorkbook.Worksheets(239).Cells(I, 8) = vzFebruar
ActiveWorkbook.Worksheets(239).Cells(I, 9) = vzMarch

Next I

End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

Sub WorksheetLoop()

Dim WS_Count As Integer

Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active

' workbook.

WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.

For I = 1 To WS_Count

' Insert your code here.

' The following line shows how to reference a sheet within

' the loop by displaying the worksheet name in a dialog box.

MA_NAME = ActiveWorkbook.Worksheets(I).Name

vJanuar = ActiveWorkbook.Worksheets(I).Cells(8, 3)

vFebruar = ActiveWorkbook.Worksheets(I).Cells(8, 4)

vMarch = ActiveWorkbook.Worksheets(I).Cells(8, 5)

vzJanuar = ActiveWorkbook.Worksheets(I).Cells(9, 3)

vzFebruar = ActiveWorkbook.Worksheets(I).Cells(9, 4)

vzMarch = ActiveWorkbook.Worksheets(I).Cells(9, 5)

ActiveWorkbook.Worksheets(239).Cells(I, 1) = MA_NAME

ActiveWorkbook.Worksheets(239).Cells(I, 3) = vJanuar

ActiveWorkbook.Worksheets(239).Cells(I, 4) = vFebruar

ActiveWorkbook.Worksheets(239).Cells(I, 5) = vMarch

ActiveWorkbook.Worksheets(239).Cells(I, 7) = vzJanuar

ActiveWorkbook.Worksheets(239).Cells(I, 8) = vzFebruar

ActiveWorkbook.Worksheets(239).Cells(I, 9) = vzMarch

Next I

End Sub

Read More →

MS SQL PIVOT with text as Fieldtype

14/02/2019

Keine Kommentare

The trick is, that you cast the text/string into varchar and then you can use MAX as aggregation type to return the readable value.

Read More →

Power BI and MS CRM Entity Tables

11/02/2019

Keine Kommentare

Temporary Entity Tables build with DAX Query and using the CRM Tables: StringMapBase MetadataSchema.Entity

Read More →

Generating a calender was a very important task within qlik sense/view – so even as the date functions within power query or dax are awesome, I still prefer to have an “inbuild” calender. To achieve this goal, you can switch to the “Modeling” view -> add a “Table” and then just copy and paste the […]

Read More →

T-SQL read tables and columns from a linked server

24/01/2019

Keine Kommentare

Read More →

T-SQL execute procedure for each row of a master table

24/01/2019

Keine Kommentare

From time to time it’s needed to loop over the rows of a master table – e.g. different companies within your organisation are stored within different databases. So to avoid creating “long” SQL-scripts where you just change the db information, you can ease it up by:

Read More →

Jedox Percentage on Percentage on Percentage Split with “instant” preview

08/11/2018

Keine Kommentare

Usecase Imagine you have a customer who wants to split revenue by multiple dimensions not based upon total figures nor on percentual splits. E.g. you plan 10k and split it to 3 Segments by 20% Seg A 40% Seg B 40% Seg C And after this by ProductGroup and Product which are in one Dimension […]

Read More →

Jedox – User CRUD – Manage Dimensions within worksheet

22/10/2018

Keine Kommentare

Usecase Sometimes it´s needed that a user can add elements to a dimension without accessing the modeller – e.g. you wanna budget personal ressources and want to allow power users to add new placeholders to the employee dimension. To get this working, you can use multiple ways on how to pass values from the worksheet, […]

Read More →

Qlik XML aus Feld in Tabelle einlesen

18/10/2018

Keine Kommentare

Ausgangslage Der Kunde liefert eine Wertetabelle, bei der in einer der Spalten XML-Daten ohne feste Struktur vorhanden sind. Diese sollen so aufbereitet werden, dass diese Struktur als eigene Felder im Datenmodel aufscheinen. Tabelle: id Attribute Attributevalue FromDate 1 1 <Attr1>123</Attr1><name>Test</name> 42005 2 2 <price>10.50</price> 41911 Vorgehen Daten ergänzen <root> node Tabelle in XML Textdatei exportieren […]

Read More →

Jedox – Daten per Integrator Job und Worksheet kopieren

23/08/2018

Keine Kommentare

Nutzen Sie möchten Ihren Nutzern eine einfache Möglichkeit zur Verfügung stellen, damit diese via Comboboxen und Button die Werte aus einer Sicht (bei Version) in eine andere Sicht (Version) kopieren können. Bsp. Saisonalität Die Saisonalität wird in einem Hilfswürfel gespeichert: <<saison_koart>> Enthalten sind die folgenden Dimensionen: Jahr Monat Version Saison_Typ (linear, effektiv oder prozentual) _measure_saison […]

Read More →

beeye – no facts?!

Excel macro loop by sheet

MS SQL PIVOT with text as Fieldtype

Power BI and MS CRM Entity Tables

Power BI Calender DAX

T-SQL read tables and columns from a linked server

T-SQL execute procedure for each row of a master table

Jedox Percentage on Percentage on Percentage Split with “instant” preview

Jedox – User CRUD – Manage Dimensions within worksheet

Qlik XML aus Feld in Tabelle einlesen

Jedox – Daten per Integrator Job und Worksheet kopieren