Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day everyone,
Normally when I do my date analysis.. my date comes in this format dd/mm/yyyy but I have a data set that has this format
Service Month | Invoice Number | Area | Invoice Value | VAT |
---|---|---|---|---|
01/01/2016 | AA101 | Area 1 | 1000 | 10 |
01/02/2016 | AB201 | Area 2 | 1400 | 14 |
01/03/2016 | AW102 | Area 2 | 3000 | 30 |
Excess and Excess Inv, 2016 | AA121 | Area 1 | 200 | 2 |
01/01/2017 | AA110 | Area 1 | 2300 | 23 |
Excess and Excess Inv, 2017 | AE123 | Area 2 | 1300 | 13 |
The Excess and Excess is more like financial activity that took place during the calendar year but the analyst can't pick the exact month (date). It's like an accrual concept.
Can you help me with the date creation?
Regards
Message was edited by: Akpofure Enughwure
Good, Are you talking about transpose data. Will you explain the result set?
What output do you want in those problematic row? Maybe alt function?
ex.
alt( date#( ServiceM , 'DD/MM/YYY' ), [function for Excess and Excess Inv, ]
'NA' )
I would love the month field to have this, Month :January, February, March, ......., Excess and Excess Inv
Year : 2016,2017
Thank you for the response
I'm not transposing
I would love the month field to have this, Month :January, February, March, ......., Excess and Excess Inv
Thank you for r
LOAD If (Index ([Service Month], 'Excess and Excess Inv') > 0,
MakeDate (Trim(SubField ([Service Month], ',', 2))),
Date (Date# ([Service Month], 'MM/DD/YYYY'), '$(DateFormat)')
) as Date,
*;
LOAD * Inline [
Service Month; Invoice Number; Area; Invoice Value;VAT
01/01/2017; AA110; Area 1; 2300; 23
Excess and Excess Inv, 2017; AE123; Area 2; 1300; 13
01/03/2016; AW102; Area 2; 3000; 30
01/02/2016; AB201; Area 2; 1400; 14
01/01/2016; AA101; Area 1; 1000; 10
Excess and Excess Inv, 2016; AA121; Area 1; 200; 2
] (delimiter is ';');
Date (Date# (Month, 'YYYYMM'), 'MMMM') as MonthName
should do the trick!