Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
RESULT
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;
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;
RESULT
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;
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
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.
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.
Ok. I'll keep that in mind next time I post a question!