topic Re: Different format in data in QlikView

Different format in data

Nagaraju_KCS — Sat, 16 Nov 2024 01:12:41 GMT

Hi Community,

I have two columns one for field and one for values

and each value has different format. like below.

Field	Value
Employee	Admin
Grade	80%
Marks	82
Salary	£12,000
Profit	8 K
Average	12.35
Date	24/02/2020

could you please help me - how to load data ?

Thanks in Advance!

Re: Different format in data

jonathandienst — Mon, 24 Feb 2020 09:29:01 GMT

This looks like a case for a generic load. Check out Generic Load in the on-line help and search the community for the topic. There are a number of very good blog posts and postings on the topic.

Re: Different format in data

Nagaraju_KCS — Mon, 24 Feb 2020 11:24:57 GMT

Thanks for quick response! -

found this example - I showed this https://community.qlik.com/t5/New-to-QlikView/Generic-Load/td-p/1231322

We don't need to create derived columns. if we do Generic load - It will create 7 columns.

but users requirement want to see only two columns. (This data coming from MongoDB.)

is there any possibility visual or script side ??

Re: Different format in data

rwunderlich — Mon, 24 Feb 2020 15:32:53 GMT

If you want only two columns, can you just load it as is? Or is your problem that the fields contain various formatting info like "%" and you want to parse everything into numeric values?

-Rob

Re: Different format in data

Nagaraju_KCS — Mon, 24 Feb 2020 16:00:56 GMT

Hi Rob,

I tried loading the data using text(FieldName) but that converts all the column values to integer values.

But I want to show integer values as integers, percent values as percent etc...

Users wants to show actual Values....

Re: Different format in data

NitinK7 — Wed, 26 Feb 2020 16:14:04 GMT

Hi,

this is very interesting scenario for me

and I am tried to find out solution, it's might be helpful for you

Note-

checking for date (Date will be 5 digit form, so check 5 digit value and convert to date.)

percentage value between 1 to 100, so check value less than 1 and convert to percentage.

ABC:
LOAD Employee,
if(Len(ceil(Trim(Admin)))=5,Date(Admin,'DD/MM/YYYY'), //Convert to date.
if(Num(Admin)<=1,Num(Admin,'#,##%'),Admin)) as Admin; // check value less than equal 1 and convert to percentage.
LOAD Employee,
if(IsText(Admin),Text(Admin),
if(IsNum(Admin),Num(Admin))) as Admin

FROM
data.xlsx
(ooxml, embedded labels, table is datatypes);

Regards,

Nitin.

Re: Different format in data

Brett_Bleess — Thu, 26 Mar 2020 12:16:05 GMT

Did the last post above mine get you a working solution? If so, do not forget to properly close out your thread by using the Accept as Solution button on that post to give the poster credit for the help and that will also let other Members know that it did work too. Please be sure to properly close out your thread, we much appreciate our Members that do so, but if you have further questions, please leave a new comment.

One link I am going to provide as well you can use in the future is that to the Design Blog area of Community, you will likely find this a very useful place to visit, lots of how-to posts on a lot of different topics, this in addition to help.qlik.com should go a long way to helping you solve things going forward.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett