Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have a simple requirement where my dimension consist of a DisbDate and ReportingDate.
Row = DisbDate
Column = ReportingDate
Measure = Sum(DisbAmt)
Find attached sample Qvf, data and Output sheet.
Data looks like :
DisbDate | 01/12/2017 | 01/11/2017 | 01/10/2017 | 01/09/2017 | 01/08/2017 | 01/07/2017 | 01/06/2017 | 01/05/2017 | 01/04/2017 | 01/03/2017 | 01/02/2017 | 01/01/2017 |
01/12/2017 | 144 | 158 | 152 | 176 | 144 | 115 | 177 | 160 | 145 | 101 | 181 | 176 |
01/11/2017 | - | 118 | 177 | 161 | 149 | 155 | 195 | 198 | 109 | 136 | 188 | 101 |
01/10/2017 | - | - | 141 | 188 | 129 | 160 | 138 | 165 | 191 | 144 | 163 | 178 |
01/09/2017 | - | - | - | 129 | 164 | 169 | 135 | 135 | 108 | 128 | 123 | 174 |
01/08/2017 | - | - | - | - | 119 | 191 | 104 | 121 | 146 | 135 | 142 | 115 |
01/07/2017 | - | - | - | - | - | 130 | 104 | 136 | 170 | 115 | 144 | 161 |
01/06/2017 | - | - | - | - | - | - | 180 | 126 | 135 | 138 | 158 | 128 |
01/05/2017 | - | - | - | - | - | - | - | 180 | 116 | 124 | 129 | 105 |
01/04/2017 | - | - | - | - | - | - | - | - | 140 | 142 | 175 | 122 |
01/03/2017 | - | - | - | - | - | - | - | - | - | 159 | 150 | 199 |
01/02/2017 | - | - | - | - | - | - | - | - | - | - | 134 | 140 |
01/01/2017 | - | - | - | - | - | - | - | - | - | - | - | 103 |
I want to each row to start from 01/12/2017 instead of nulls.
I have also added a Sort field which would guide each row as how much columns to shift on the left. (Ref: Data Sheet, Tab2). The output should look as attached in "RequiredOutput" (Excel)
Would prefer something at Visual layer instead of load script.
Dear Niky,
As per my understanding :
Kindly find the attached application i hope it will fulfill your requirement,
Thanks,
Mukram.
No buddy , this isn't that straight forward and sorting months will not help. I want the same output as attached in RequiredOutput excel
Some script changes will be needed to get this
New Script
MainTable:
LOAD
DisbDate,
DisbAmt,
ReportingDate,
DisbDate&ReportingDate as Key
FROM [lib://Lib/DataSheet.xlsx]
(ooxml, embedded labels, table is Tab1);
TempTable:
LOAD DisbDate
Resident MainTable;
Join (TempTable)
LOAD ReportingDate
Resident MainTable;
Concatenate (MainTable)
LOAD *
Resident TempTable
Where not Exists(Key, DisbDate&ReportingDate);
Drop Table TempTable;
MonthsToBeAdded:
LOAD DisbDate,
Sort
FROM [lib://Lib/DataSheet.xlsx]
(ooxml, embedded labels, table is Tab2);
Basically added the missing combination of data between DisbDate and ReportingDate
and then this expression
After(Sum(DisbAmt), Sort)
Thanks Sunny !!
Would prefer something at the front end as editing load script is not possible currently.
Don't think it is possible, but let's see if someone can come up with a solution.
Best,
Sunny