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: 
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
maxgro
MVP
MVP

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;

View solution in original post

7 Replies
MK_QSL
MVP
MVP

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;

maxgro
MVP
MVP

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

Scripting.PNG

MarcoWedel

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
Author

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.

maxgro
MVP
MVP

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
Author

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