Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am looking on how to populate numbers sequentially.
I have the following table below with 3 clients. If a client first meets a condition, it gets a primary flag of "1". (highlight in yellow below). So for Client A, first "!" is Mar 18, for B its Jan 18 and for C is Apr 18.
I want to create a field in the backend, where the subsequent months gets a cumulative number and preceding months will be negative. So solution I'm looking for is Column "Answer" in the table below.
Please can you help. Thanking you in advance.
Kind regards
Nayan
Client | Month Number | Year | Month Name | PrimaryFlag | Answer |
A | 806 | 2017 | Mar | 0 | -11 |
A | 807 | 2017 | Apr | 0 | -10 |
A | 808 | 2017 | May | 0 | -9 |
A | 809 | 2017 | Jun | 0 | -8 |
A | 810 | 2017 | Jul | 0 | -7 |
A | 811 | 2017 | Aug | 0 | -6 |
A | 812 | 2017 | Sep | 0 | -5 |
A | 813 | 2017 | Oct | 0 | -4 |
A | 814 | 2017 | Nov | 0 | -3 |
A | 815 | 2017 | Dec | 0 | -2 |
A | 816 | 2018 | Jan | 0 | -1 |
A | 817 | 2018 | Feb | 0 | 0 |
A | 818 | 2018 | Mar | 1 | 1 |
A | 819 | 2018 | Apr | 0 | 2 |
B | 804 | 2017 | Jan | 0 | -11 |
B | 805 | 2017 | Feb | 0 | -10 |
B | 806 | 2017 | Mar | 0 | -9 |
B | 807 | 2017 | Apr | 0 | -8 |
B | 808 | 2017 | May | 0 | -7 |
B | 809 | 2017 | Jun | 0 | -6 |
B | 810 | 2017 | Jul | 0 | -5 |
B | 811 | 2017 | Aug | 0 | -4 |
B | 812 | 2017 | Sep | 0 | -3 |
B | 813 | 2017 | Oct | 0 | -2 |
B | 814 | 2017 | Nov | 0 | -1 |
B | 815 | 2017 | Dec | 0 | 0 |
B | 816 | 2018 | Jan | 1 | 1 |
B | 817 | 2018 | Feb | 1 | 2 |
B | 818 | 2018 | Mar | 1 | 3 |
B | 819 | 2018 | Apr | 1 | 4 |
C | 808 | 2017 | May | 0 | -10 |
C | 809 | 2017 | Jun | 0 | -9 |
C | 810 | 2017 | Jul | 0 | -8 |
C | 811 | 2017 | Aug | 0 | -7 |
C | 812 | 2017 | Sep | 0 | -6 |
C | 813 | 2017 | Oct | 0 | -5 |
C | 814 | 2017 | Nov | 0 | -4 |
C | 815 | 2017 | Dec | 0 | -3 |
C | 816 | 2018 | Jan | 0 | -2 |
C | 817 | 2018 | Feb | 0 | -1 |
C | 818 | 2018 | Mar | 0 | 0 |
C | 819 | 2018 | Apr | 1 | 1 |
Perhaps like this:
mapPrimaryMonth
MAPPING LOAD
Client,
[Month Number]
FROM
...source table...
WHERE
[PrimaryFlag] = 1
;
Result:
LOAD
Client,
[Month Number],
Year,
[Month Name],
PrimaryFlag
1+[Month Number]-ApplyMap('mapPrimaryMonth',Client,9999) as Answer
FROM
...source table...
;
Perhaps like this:
mapPrimaryMonth
MAPPING LOAD
Client,
[Month Number]
FROM
...source table...
WHERE
[PrimaryFlag] = 1
;
Result:
LOAD
Client,
[Month Number],
Year,
[Month Name],
PrimaryFlag
1+[Month Number]-ApplyMap('mapPrimaryMonth',Client,9999) as Answer
FROM
...source table...
;
Thank you Gysbert. Will try it out and let you know.
Kind regards
Nayan
Hi,
try this
INPUT:
LOAD Client,
[Month Number],
Year,
[Month Name],
PrimaryFlag
,Answer
FROM
(ooxml, embedded labels, table is Sheet3);
INPUT1:
LOAD distinct Client, Year as Year1, [Month Name] as [Month Name1]
Resident INPUT
where PrimaryFlag = 1;
left join
load
Client,
[Month Number],
Year,
[Month Name], PrimaryFlag
Resident INPUT;
DROP Table INPUT;
INPUT2:
LOAD Client, [Month Number],
Year,
[Month Name], PrimaryFlag,
Num(Month(Date#([Month Name],'MMM'))) as t,
(12 * (Year - Year1) + Num(Month(Date#([Month Name],'MMM'))) - Num(Month(Date#([Month Name1],'MMM'))))+1 as Answer
resident INPUT1;
DROP Table INPUT1;
But correct your excel file because you must have only one primaryflag = 1 for each client
HTH
André Gomes