Apply conversion in script for seconds, text and data calculations

[martin_hamilton]

Hi

I have loaded which creates a table with columns below:

BUNDLE_UOM	BUNDLE_AMOUNT	BUNDLE_USED	BUNDLE_REMAINING
DATA	1024	0	1024
MINUTES	30000	0	30000
TEXT	200	0	200

I wish to convert the values based on the BUNDLE_UOM. e.g.

For the DATA BUNDLE_UOM the value needs to be converted from KB to MB i.e divided by 1024- so the new column value would be 1

For the MINUTES BUNDLE_UOM the value needs to be converted from seconds to minutes i.e divided by 60 so the new column value would be 500

For the TEXT BUNDLE_UOM the value would remain the same so divided by 1.

My preferred approach would be to do the calculation within the load script and create new columns and I would have a reference table which would hold the calculations for each of the differing types of BUNDLE_UOM. I think it would work the same as if you was converting current exchange rates.

Is there a simple way to do this? I have attached a sample.

Many Thanks

Martin

[Anil_Babu_Samineni]

I don't have license at this time

Yes we can using preceding load like it acts as having clause in database.

Load firstname, lastname, firstname & lastname as fullname;

Load * inline [

Firstname, lastname

A, abc

B, xyz

];

[adamdavi3s]

If you want to do it on the fly in the script here is the quick way

UOMCONVERSTION:

LOAD *,

IF(BUNDLE_UOM = 'DATA', Rate /1024,

  IF(BUNDLE_UOM = 'Voice', Rate /60,

  IF(BUNDLE_UOM = 'SMS', Rate,0

  )

  )

) as newrate;

LOAD * INLINE [

BUNDLE_UOM,Rate

VOICE,60

DATA,1024

SMS,1];

[adamdavi3s]

Hi,

Obviously I can't load your data to test this but check this out.

Create a mapping table and then use this in a preceding load to work out your new columns

//create a map
UOMCONVERSTION:
MAPPING LOAD * INLINE [
    Sub_Bundle_UOM, divisor
    DATA,1024
    MINUTES,60
    TEXT,1

];










BUNDLEEXTENSION:


LOAD
A1QDCD as   Bundle_Code,                                     
A1ZICD as   Bundle_UOM      


FROM
$(vPathRefData)C8A1REP.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq); 




BUNDLELEVEL:


LOAD Value as Bundle_Level, 
     Description as Bundle_Level_Description
FROM
$(vPathRefData)CMPDatabaseValueLookUps.xlsx
(ooxml, embedded labels, table is BundleLevel);




SUBSCRIPTIONBUNDLE:
//do some preceeding loads to work out the maths
LOAD *, Bundle_Remaining / divisor as new_bundle_remaining;


LOAD *, applymap('UOMCONVERSTION',Sub_Bundle_UOM) as divisor;


LOAD


NIB8NB as Sub_Number,
NIQDCD as Sub_Bundle_Bundle_Code,
Date(Date#(Right(NILMDT, 6), 'YYMMDD')) as Sub_Bundle_Effective_Date,                           
NIB6NB as Sub_Bundle_Agreement_Number,                                         
NISONB as Sub_Bundle_Bundle_Sequence,                                   
NIECST as Sub_Bundle_Bundle_Level,                                    
Date(Date#(Right(NILYDT, 6), 'YYMMDD')) as Sub_Bundle_Expiry_Date,                               
NIRPNA as Sub_Bundle_Description,
IF(0 < ([NILYDT]) , 'Expired', 'Active') as [Bundle_Status],
Lookup('Bundle_Level_Description','Bundle_Level',[NIECST],'BUNDLELEVEL') as [Sub_Bundle_Level_Description],
Lookup('Bundle_UOM','Bundle_Code',[NIQDCD],'BUNDLEEXTENSION') as [Sub_Bundle_UOM]


FROM
$(vPathCustomerData)CQNIREP.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);


JOIN ALLOCATEDBUNDLE:


LOAD


NMB8NB as Sub_Number,
NMQDCD as Sub_Bundle_Bundle_Code,
NMPSNB as Bundle_Allocation_Number,
NMB6NB as Bundle_Agreement_Number,
NMBCDT as Bundle_Last_Call_Date,                                    
NMDKTM as Bundle_Last_Call_Time,
NMB6NB as Agreement_Number,
NMLNDT as Bundle_Period_Start_Date,                          
NMLODT as Bundle_Period_End_Date,                            
NMPGDT as Allocated_Bundle_Expiry_Date                       




FROM
$(vPathCustomerData)CQNMREP.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);


JOIN BUNDLEALLOCATIONBAND:


LOAD


N3PSNB as   Bundle_Allocation_Number,                          
N3PRNB as   Bundle_Amount,                                 
N3PUNB as   Bundle_Remaining,
[N3PRNB]- [N3PUNB] as Bundle_Used                                          


FROM


$(vPathCustomerData)CRN3REP.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);