Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

evgeniystuchalk
Contributor II

Create multiple records from single line

Hello! I have table with payments. Like:

7cd818a228.jpg

I want to generate additional table during load, where for each Deal I have record for each month that included in Pack. Like:

7cc495972e.jpg

To make this connection:

9ae8e5d24f.jpg

How I can do it?

1 Solution

Accepted Solutions
MVP
MVP

Re: Create multiple records from single line

Something like

LOAD [Deal ID],

          [Price of Pack] / [Month in Pack] AS [Accural Sum],

          Date( AddMonths([Purchase Date], iterno()-1) ) AS [Accural Date]

RESIDENT YourTable

WHILE iterno() <= [Month in Pack];

5 Replies

Re: Create multiple records from single line

Hi,

You have to use For Loop or Iterno() functions for this on the load script data model.

See some examples on the community on this topics.

Regards,

Anand

MVP
MVP

Re: Create multiple records from single line

Something like

LOAD [Deal ID],

          [Price of Pack] / [Month in Pack] AS [Accural Sum],

          Date( AddMonths([Purchase Date], iterno()-1) ) AS [Accural Date]

RESIDENT YourTable

WHILE iterno() <= [Month in Pack];

Re: Create multiple records from single line

Try this:

Table:

LOAD PricePerMonth,

  DealID,

  AddMonths(PurchaseDate, IterNo() - 1) as PurchaseDate

While IterNo() <= Month;

LOAD *,

  Price/Month as PricePerMonth

Inline [

Price, PurchaseDate, Month, DealID

6000, 18.02.2016, 6, 1

5000, 01.02.2016, 4, 2

];


Capture.PNG

Re: Create multiple records from single line

Hi,

Try this ways

Temp:

LOAD PriceOfPack / MonthinPack as PriceOfPack, AddMonths( PurDate, IterNo()-1) as PurDate ,MonthinPack,DealId,IterNo() as ID

While IterNo() <= MonthinPack;

LOAD PriceOfPack,Date#(PurDate,'DD.MM.YYYY') AS PurDate,MonthinPack,DealId;

LOAD * Inline

[

PriceOfPack,PurDate,MonthinPack,DealId

6000,18.02.2016,6,1

5000,01.02.2016,4,2

];

Regards

Anand

evgeniystuchalk
Contributor II

Re: Create multiple records from single line

exactly what I need Thanks

Community Browser