topic Re: JD Edwards Qlikview in QlikView

JD Edwards Qlikview

shalensookdeo — Sat, 16 Nov 2024 21:17:56 GMT

Hi All

Please can i have some advise on the attached file. This is data that feeds from JD Edwards.

In the columns the months are in the format of (Net Posting 01) which is July and (Net Posting 02) which is Aug and (Net Posting 3) which is Sep on so on.

How can load the data into qlikview so that i can select month July for example and see my totals.

I have tried a cross table and lose some columns after loading.

Any sample files would be appreciated.

Re: JD Edwards Qlikview

Shubham_Deshmukh — Wed, 06 Mar 2019 09:27:31 GMT

Have you tried Case WHen in SQL script

Re: JD Edwards Qlikview

shalensookdeo — Wed, 06 Mar 2019 09:30:09 GMT

HI
Yes i have tried, the data is same in SQL.
Thanks

Re: JD Edwards Qlikview

Shubham_Deshmukh — Wed, 06 Mar 2019 09:47:34 GMT

Let me understand your requirement, you have Net Posting 01,Net Posting 02 instead of Month names.So you just want to replace the same by month names ?
If yes, then case when should work in your case.

Re: JD Edwards Qlikview

shalensookdeo — Wed, 06 Mar 2019 09:51:53 GMT

Yes and also be able to select each month separately on my selections to see the totals.

Re: JD Edwards Qlikview

Shubham_Deshmukh — Wed, 06 Mar 2019 09:53:53 GMT

Hi,
So whats an issue? Show me your case when.
Ya, once you got your mapping correct, filter will work as you want.

Re: JD Edwards Qlikview

shalensookdeo — Wed, 06 Mar 2019 09:56:55 GMT

How would you map this ? I am having trouble with making the months a selectable filter.
Thanks

Re: JD Edwards Qlikview

Shubham_Deshmukh — Wed, 06 Mar 2019 10:00:57 GMT

Firstly use proper Case When in your script for proper association with all data,

Case

When field='Net Posting 01' then 'Jul'

When field='Net Posting 02' then 'Aug'

When field='Net Posting 03' then 'Sep'

end as 'month';

Use month in list box.

Re: JD Edwards Qlikview

abednego — Wed, 06 Mar 2019 12:27:11 GMT

When you load the excel file, rename the headings to whatever month you desire. I noticed that you have 14 months instead of 12 and this could be the reason why you lose data when you import two columns with the same name. I also noticed that the same header names appear with 'Debit' in them. Attached is my sample import of your file.

Re: JD Edwards Qlikview

shalensookdeo — Wed, 06 Mar 2019 13:34:57 GMT

Hi Abednego
Thank you for the assistance but how would i select each month for example if i select just July as a selection or if i want to select July and August Even if i need to create another excell table with the months?
Thanks

Re: JD Edwards Qlikview

anushree1 — Wed, 06 Mar 2019 13:50:46 GMT

Well that depends on how you want to look at the data , so would you like to have separate months for Debit and net data or should it be combined and how you would like to deal with Columns 13 and 14

Re: JD Edwards Qlikview

hopkinsc — Wed, 06 Mar 2019 14:02:03 GMT

what about the attached.?

Note that columns 13/14 are in the Dims tab as i don't know what they are.

And the Key is guessed, so might need changing

Re: JD Edwards Qlikview

shalensookdeo — Wed, 06 Mar 2019 14:35:52 GMT

Well actually it should be combined, When i select a month then it must show me a sum of all the Debit or sum of all the credits but this i can do later in the chart box. For now i just need to get the months to work as a slectable box. See my sample attached.

Re: JD Edwards Qlikview

shalensookdeo — Wed, 06 Mar 2019 14:49:09 GMT

Wow
This seems good, May be just what i am trying to achieve.
Great stuff .
Much appreciated

Re: JD Edwards Qlikview

hopkinsc — Wed, 06 Mar 2019 14:50:52 GMT

if you want to combine them then maybe something like the attached?

Re: JD Edwards Qlikview

shalensookdeo — Thu, 07 Mar 2019 09:20:58 GMT

Good Morning
So the excell file i supplied is an extract of the data from my database. If i try and connect your qvw to my database with the same script except that i point the script to my live database will i need to make any other changes for it to work?
Thanks

Re: JD Edwards Qlikview

hopkinsc — Thu, 07 Mar 2019 09:26:46 GMT

Hi, has there been any renaming done in the excel extract?

JDE field names in the db are nothing like what is in the excel file so i am guessing you exported it from a front end UI table? if yes and you need to get the data directly from the JDE database then you will need to know what the JDE field names are for the columns needed.

Re: JD Edwards Qlikview

shalensookdeo — Thu, 07 Mar 2019 09:55:13 GMT

Yes the data seems like it is slightly different on the excell. I have extracted it from the UI table.

Re: JD Edwards Qlikview

hopkinsc — Thu, 07 Mar 2019 09:52:31 GMT

Then yes, you will need to replace the excel field names with the original JDE names. there might be further changes needed if the original fields are not all in the same JDE table too.

Re: JD Edwards Qlikview

shalensookdeo — Thu, 07 Mar 2019 11:55:15 GMT

I seem to have it working now 🙂
Only challenge is how would i change the amounts to reflect correctly. It is currently showing as per example 828 493 .00 but in actual fact it supposed to be 8 284.93?