Creating flag based on different Excel Formatting

[sunny_talwar]

Hi Guys -

I am wondering if it is possible to load data from Excel while keeping its format or having some kind of identifier that would tell me that the data was in Dollar or %age.

My Excel looks like this:

Ideally, I am looking for a way to create this table:

Field	Format
$0.25 or 0.25	Money
25% or 0.25	Percentage
$0.35 or 0.35	Money
35% or 0.35	Percentage

I am not so much concern on how the Field itself is loaded, but I want to create some sort of flag which would help me differentiate between $ inputs vs. $ inputs.

I am attaching my Excel for convenience.

Thanks in advance for help.

swuehl‌jagan‌pcammaert‌MRKachhiaIMP‌jontydkpi‌Marcus_Sommer‌bill.markham

Best,

Sunny

[marcus_sommer]

Hi Sunny,

I think this will be only possible if those columns are string-values and not numeric with a formatting. This meant you will need this in excel as string or an additionally field which covered the formatting or you could copy these data to notepad and load it from there as txt-file (also a saving the excel as csv might help).

- Marcus

[Anonymous]

If you save the xlsx in csv format and load from that, then your data gets loaded in text format that could then be manipulated as per your needs.

Maybe could somehow get the input file formatted as csv ?

[sunny_talwar]

Marcus -

Do you know if there might be a Macro which might be able to help pick the formatting? There is a GetFormat() macro which works in Excel, is it possible to bring that macro into QlikView to determine the formatting?

[marcus_sommer · Accepted solution]

Could you use a function like this in excel (maybe in a hidden column)?

=WENN(ZELLE("Format";A2)=".2";"$";WENN(ZELLE("Format";A2)="P0";"%";"#NV")) // in german

=IF(CELL("Format";A2)=".2";"$";IF(CELL("Format";A2)="P0";"%";"#NV")) // probably in englisch

- Marcus

[sunny_talwar]

Thanks Marcus, this sounds like a feasible option to explore. Let me give this a try and get back to you.

Best,

Sunny

[PradeepReddy]

see the attachment...

[sunny_talwar]

Thanks Pradeep. This look promising and I will propose this method to my colleagues and see what they say. Thanks for the sample