topic Populating numbers sequentially in QlikView

Populating numbers sequentially

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Populating numbers sequentially

Gysbert_Wassenaar — Mon, 04 Jun 2018 16:15:30 GMT

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...

;

Re: Populating numbers sequentially

Anonymous — Mon, 04 Jun 2018 16:30:32 GMT

Thank you Gysbert. Will try it out and let you know.

Kind regards

Nayan

Re: Populating numbers sequentially

agomes1971 — Mon, 04 Jun 2018 16:33:23 GMT

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