topic Re: Create new rows from existing records in QlikView

Create new rows from existing records

Thu, 29 Oct 2015 20:32:13 GMT

Hi everyone,

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

I have an existing dataset that looks like this:

ID	Market	Amount
H91	Product	$100
B475D	Service	$150
XH1	Other	$75
AB85	Modeled	$200

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

Market	%Breakdown
Product	50%
Service	30%
Other	20%

The result would look like this:

ID	Market	Amount
H91	Product	$100
B475D	Service	$150
XH1	Other	$75
AB85_1	Product	$100
AB85_2	Service	$60
AB85_3	Other	$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?

Re: Create new rows from existing records

MK_QSL — Thu, 29 Oct 2015 20:40:57 GMT

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;

Re: Create new rows from existing records

maxgro — Thu, 29 Oct 2015 20:51:05 GMT

RESULT

SCRIPT

load * inline [

ID, Market, Amount

H91, Product, 100

B475D, Service, 150

XH1, Other, 75

AB85, Modeled, 200

AA85, Modeled, 100

CC32, Modeled, 300

];

load * inline [

Market, Breakdown

Product, 50

Service, 30

Other, 20

];

join (B)

load ID, Amount

Resident A

Where Market = 'Modeled';

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;

Re: Create new rows from existing records

prabhu0505 — Thu, 29 Oct 2015 21:00:45 GMT

Re: Create new rows from existing records

MarcoWedel — Thu, 29 Oct 2015 23:21:50 GMT

Hi,

another solution could be:

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

Re: Create new rows from existing records

Fri, 30 Oct 2015 00:21:13 GMT

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.

Re: Create new rows from existing records

maxgro — Fri, 30 Oct 2015 08:22:11 GMT

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.

Re: Create new rows from existing records

Fri, 30 Oct 2015 13:05:22 GMT

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