topic Re: Apply conversion in script for seconds, text and data calculations in QlikView

Apply conversion in script for seconds, text and data calculations

martin_hamilton — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Apply conversion in script for seconds, text and data calculations

Anil_Babu_Samineni — Thu, 29 Sep 2016 08:57:33 GMT

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

];

Re: Apply conversion in script for seconds, text and data calculations

adamdavi3s — Thu, 29 Sep 2016 08:57:36 GMT

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];

Re: Apply conversion in script for seconds, text and data calculations

adamdavi3s — Thu, 29 Sep 2016 09:04:05 GMT

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);