Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Populating numbers sequentially

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

  

ClientMonth NumberYearMonth NamePrimaryFlagAnswer
A8062017Mar0-11
A8072017Apr0-10
A8082017May0-9
A8092017Jun0-8
A8102017Jul0-7
A8112017Aug0-6
A8122017Sep0-5
A8132017Oct0-4
A8142017Nov0-3
A8152017Dec0-2
A8162018Jan0-1
A8172018Feb00
A8182018Mar11
A8192018Apr02
B8042017Jan0-11
B8052017Feb0-10
B8062017Mar0-9
B8072017Apr0-8
B8082017May0-7
B8092017Jun0-6
B8102017Jul0-5
B8112017Aug0-4
B8122017Sep0-3
B8132017Oct0-2
B8142017Nov0-1
B8152017Dec00
B8162018Jan11
B8172018Feb12
B8182018Mar13
B8192018Apr14
C8082017May0-10
C8092017Jun0-9
C8102017Jul0-8
C8112017Aug0-7
C8122017Sep0-6
C8132017Oct0-5
C8142017Nov0-4
C8152017Dec0-3
C8162018Jan0-2
C8172018Feb0-1
C8182018Mar00
C8192018Apr11
1 Solution

Accepted Solutions
Gysbert_Wassenaar

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

     ;


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

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

     ;


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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

Kind regards

Nayan

agomes1971
Specialist II
Specialist II

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