Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
MVP & Luminary
MVP & Luminary

Re: Populating numbers sequentially

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
MVP & Luminary
MVP & Luminary

Re: Populating numbers sequentially

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

Highlighted
Creator
Creator

Re: Populating numbers sequentially

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

Kind regards

Nayan

Highlighted
Specialist II
Specialist II

Re: Populating numbers sequentially

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