Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have two excel file with some data type changes.
Excel 1:
The amount value has % symbol, while reading in qlikview in converts to number. (i.e 5% to 0.05)
Name | Amount |
A | 5% |
B | 6.50% |
Excel 2:
Here, the amount value showing as a number only. but still it is a percentage. (i.e 8 is a 8%)
Name | Amount |
C | 8 |
D | 6.7 |
Now I want to concatenate the above two table into one table and Amount Field should be one format . either with percent % symbol or value.
Result: (Concatenated Table)
Name | Amount |
A | 0.050 |
B | 0.065 |
C | 0.080 |
D | 0.067 |
How can I achieve this?
Hi,
Try with:
Data:
Load
Name,
SubField(Amount, '%', 1)/100 as Amount
Inline [
Name, Amount
A, 5%
B, 6.50%
]
;
Concatenate
Load
Name,
SubField(Amount, '%', 1)/100 as Amount
Inline [
Name, Amount
C, 8
D, 6.7
]
;
Saludos
Hi Federico,
Thanks for your reply.
Both files are reading from excel file. while reading values from excel file, i am not getting the % symbol.
I got only values. (ex:5% as 0.05 )
Hi,
If you got numbers after load you can format the field where you want to use it.
Can you share a sample of your excel files?
Saludos.
Hi Feder,
PFA.,
Hi,
Sorry for the delay.
TRy with:
Data:
LOAD Name,
Amount
FROM
(ooxml, embedded labels, table is Sheet1);
LOAD
Name,
Amount/100 as Amount
FROM
(ooxml, embedded labels, table is Sheet1);
Saludos