Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Create new rows from existing records

Hi everyone,

I don't know if this possible - I think it is.

I have an existing dataset that looks like this:

 

IDMarketAmount
H91Product$100
B475DService$150
XH1Other$75
AB85Modeled$200

I want to take all IDs within Market = 'Modeled' and break them out into multiple new IDs using this breakout table:

  

Market%Breakdown
Product50%
Service30%
Other20%

The result would look like this:

 

IDMarketAmount
H91Product$100
B475DService$150
XH1Other$75
AB85_1Product$100
AB85_2Service$60
AB85_3Other$40

If it's not possible to add an underscore + number to ID, that's fine.

Does anyone know how to do this in the script? Or even in an object if necessary?

1 Solution

Accepted Solutions
MVP
MVP

Re: Create new rows from existing records

RESULT


1.png


SCRIPT


A:

load * inline [

ID, Market, Amount

H91, Product, 100

B475D, Service, 150

XH1, Other, 75

AB85, Modeled, 200

AA85, Modeled, 100

CC32, Modeled, 300

];

B:

load * inline [

Market, Breakdown

Product, 50

Service, 30

Other, 20

];

join (B)

load ID, Amount

Resident A

Where Market = 'Modeled';

C:

NoConcatenate load *

Resident A

where Market <> 'Modeled';

Concatenate (C) load

ID & '_' & num(AutoNumber(rowno(), ID), '00') as ID,

Market,

Amount*Breakdown/100 as Amount

Resident B

order by ID;

DROP Table A, B;

7 Replies

Re: Create new rows from existing records

Data:

Load * Inline

[

  ID, Market, Amount

  H91, Product, 100

  B475D, Service, 150

  XH1, Other, 75

  AB85, Modeled, 200

];

NoConcatenate

T1:

Load

  ID & '_' &IterNo() as ID,

  IF(IterNo() = 1, 'Product', If(IterNo()= 2, 'Service','Other')) as Market,

  IF(IterNo() = 1, Amount * 0.5, If(IterNo()= 2, Amount * 0.3,Amount * 0.2)) as Amount

Resident Data

While IterNo() <= 3

And Market = 'Modeled';

Concatenate

Load

  ID,

  Market,

  Amount

Resident Data

Where Market <> 'Modeled';

Drop Table Data;

MVP
MVP

Re: Create new rows from existing records

RESULT


1.png


SCRIPT


A:

load * inline [

ID, Market, Amount

H91, Product, 100

B475D, Service, 150

XH1, Other, 75

AB85, Modeled, 200

AA85, Modeled, 100

CC32, Modeled, 300

];

B:

load * inline [

Market, Breakdown

Product, 50

Service, 30

Other, 20

];

join (B)

load ID, Amount

Resident A

Where Market = 'Modeled';

C:

NoConcatenate load *

Resident A

where Market <> 'Modeled';

Concatenate (C) load

ID & '_' & num(AutoNumber(rowno(), ID), '00') as ID,

Market,

Amount*Breakdown/100 as Amount

Resident B

order by ID;

DROP Table A, B;

prabhu0505
Valued Contributor

Re: Create new rows from existing records

Scripting.PNG

Re: Create new rows from existing records

Hi,

another solution could be:

QlikCommunity_Thread_189731_Pic1.JPG

tab1:

LOAD RecNo() as BrdwnID, *

Inline [

    Market, %Breakdown

    Product, 50%

    Service, 30%

    Other, 20%

];

tab2:

LOAD If(Market='Modeled',ID&'_'&IterNo(),ID) as ID,

    If(Market='Modeled',Lookup('Market','BrdwnID',IterNo(),'tab1'),Market) as Market,

    If(Market='Modeled',Money(Amount*Lookup('%Breakdown','BrdwnID',IterNo(),'tab1'),'$#,##0;($#,##0)'),Amount) as Amount

Inline [

    ID, Market, Amount

    H91, Product, $100

    B475D, Service, $150

    XH1, Other, $75

    AB85, Modeled, $200

]

While IterNo()<=If(Market='Modeled',NoOfRows('tab1'),1);

DROP Table tab1;

hope this helps

regards

Marco

Not applicable

Re: Create new rows from existing records

Thank you all for your responses! Manish, I gave the correct answer to Maxgro because my real dataset actually has more than 3 values in the Market field, and the inline statement for Breakdown % also allows me to keep my values in a separate file, so his script was easier to adapt. But thank you so much for your fast response.

Highlighted
MVP
MVP

Re: Create new rows from existing records

You can mark helpful the answer of the people (Manish, Saravana, Marco) with correct answer but not the one you prefere. The real help from the community is to have different ways to answer the same question.

Not applicable

Re: Create new rows from existing records

Ok. I'll keep that in mind next time I post a question!

Community Browser