topic Re: Creating flag based on different Excel Formatting in QlikView

Creating flag based on different Excel Formatting

sunny_talwar — Fri, 19 Feb 2016 10:02:15 GMT

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

Re: Creating flag based on different Excel Formatting

marcus_sommer — Fri, 19 Feb 2016 10:14:46 GMT

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

Re: Creating flag based on different Excel Formatting

Anonymous — Fri, 19 Feb 2016 10:18:18 GMT

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 ?

Re: Creating flag based on different Excel Formatting

sunny_talwar — Fri, 19 Feb 2016 10:18:51 GMT

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?

Re: Creating flag based on different Excel Formatting

marcus_sommer — Fri, 19 Feb 2016 11:42:47 GMT

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

Re: Creating flag based on different Excel Formatting

sunny_talwar — Fri, 19 Feb 2016 11:44:22 GMT

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

Best,

Sunny

Re: Creating flag based on different Excel Formatting

PradeepReddy — Fri, 19 Feb 2016 12:49:50 GMT

see the attachment...

Re: Creating flag based on different Excel Formatting

sunny_talwar — Fri, 19 Feb 2016 12:59:49 GMT

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