Renaming fields using variable

[polisetti]

Hi Everyone,

My requirement is to change the table fields value to logical months by using another field. Below is the example:

Actual Table contains a field name as below and Data month field always contains one specific Month value- '1/1/2016' in DD/MM/YYYY format.

Now I need to change the below columns accordingly. ie.., Month 1 should change to the value present in Data Month and Month 2 should be Data date-1 and so on till Month 24.

Table Fileds	I need
Customer	Costomer
Address	Address
Data Month	Data Month
Month 1	Jan'16
Month 2	Dec'15
Month 3	Nov'15
Month ...	Oct'
Month 24	Jan'14

Could someone please guide me how to do this at the script level.

Thanks,

Jaswanth

[kaushiknsolanki]

Hi,

Below link will help.

http://www.learnallbi.com/renaming-fields-in-qlikview-using-mapping-load/

Regards,

Kaushik Solanki

[Anil_Babu_Samineni]

How you are calculating for I need??

What are the Month 1, Month 2, --- Month 24

[ramasaisaksoft]

Hi Jaswanth,

If possible create Inline load

else create a resident load with new column added

[polisetti]

Thanks Everyone,

Here my requirement is something different to others. So, I cannot use mapping table because mapping table will be used for static column names. However, I need dynamic column names which will change in every run if Data date field has a new value.

I don't think Inline and Mapping table will be useful in this case. I need to pass Data Date value into a variable and need to write some loop type syntax. So, whenever there is a change in Data Date - the month's field names needs to be updated accordingly.

Please do the needful.

[marcus_sommer]

Beside the renaming topic I think you should consider The Crosstable Load‌ to get a "normal" data-structure - and if you used a crosstable you could create your period-values with an expression like:

addmonths(makedate(2016, 1, 1), keepchar(YourPeriodField, '0123456789') * - 1) as Date

- Marcus

[t_moutault]

Hi,

try this :

Month1 = MonthName(Date#([Data month],'DD/MM/YYYY')

Month2 = MonthName(AddMonth(Date#([Data month],'DD/MM/YYYY'),-1)

Month3 = MonthName(AddMonth(Date#([Data month],'DD/MM/YYYY'),-2)

.

.

Month24 = MonthName(AddMonth(Date#([Data month],'DD/MM/YYYY'),-23)

hope this help !

[polisetti]

Thanks Marcus,

The problem we are facing - Row count of the table is around 50 million records and if we convert months to pivot down- the row count will increase to 50 million*24 =1 billion 200 million records which will impact on the performance of reports and can also crash the QlikView reports.

Is there any other way to handle this problem?

-Jaswanth P

[marcus_sommer]

I think you will need to try several ways to create your datamodel and to design your objects and expressions within the gui to find a solution which fits to your performance-requirements (especially because there are probably some more tables and requirements and maybe X concurrent user - anywhere (CPU, RAM, ...) you will have your biggest bottleneck which might force you to a solution which will generaly work but could be a nightmare of developing and maintaining them ...

Of course to transform a big table of 50 M records per crosstable will take some time and RAM consumption (maybe the load could be sliced into several buckets and/or an incremental approach could be applied). Nevertheless this would be my first approach and I assume that aggregating 1200 M records over one single field would not perform slower than 50 M records over 24 fields - rather the opposite and the handling with one field is a lot easier as by 24 fields.

- Marcus

[Anonymous · Accepted solution]

Hi Jaswanth,

With this code you can generate the fields that you want and rename it. Only change MakeDate(2016,1,1) by your max date or the Today date.

LET vNumRows = 24;

FOR i = 1 to $(vNumRows) 
    Data:
    LOAD 'Month $(i)' as Month
    AutoGenerate 1;
Next i

FOR i = 1 to $(vNumRows) 
    LET vMonthField = Peek('Month',$(i)-1,'Data');
    DataNames:
    LOAD
        '$(vMonthField)' as MonthField,
        Capitalize(Text(Date(AddMonths(MakeDate(2016,1,1),-($(i)-1)),'MMM. YY'))) as NewMonthField
    AutoGenerate 1;
NEXT i

MappNames:
Mapping 
LOAD 
    MonthField, 
    NewMonthField 
RESIDENT DataNames;

RENAME Fields using MappNames;

Regards!!!