Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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 ??
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
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....
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.
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