topic Re: Create a custom table in QlikView

Create a custom table

amber2000 — Thu, 13 Nov 2014 16:43:16 GMT

Dear Qlikview experts,

I would like to create a chart or table in a special kind of form and I don't know if it's even possible.

There are 4 fields and I wish to put it in a chart or table like this:

Vendor_Name	Aisle_Desc	1/10/2014	2/10/2014	3/10/2014	4/10/2014	6/10/2014
DANONE	PBL_Zuivel	10:30:06	10:44:39	11:49:40	8:43:02	12:00:05
DANONE	Zuivel_Gekoeld	10:30:06	10:44:39	11:49:40	8:43:02	12:00:05
DELPEYRAT SA	PBL_Traiteur	-	8:25:48	8:03:31	-	7:21:14
DELPEYRAT SA	PBL_Traiteur	10:56:33	9:20:12	9:37:57	-	7:16:31
DENDERLUX	PBL_Vlees	8:07:33	9:10:24	8:27:26	8:00:51	9:08:34
DENDERLUX	PBL_Vlees	8:07:33	9:10:24	8:27:26	8:00:51	9:08:34

If I use a pivottable I see only one field and I want to see Vendor_Name and Aisle_Desc next to each other.

To see the data in it's original state I'll add an qvw example.

Does anybody knows how to acomplish this please?

Kind regards,

Ambertje

Re: Create a custom table

Thu, 13 Nov 2014 17:11:20 GMT

If you change expression to sum() and select Always Fully expanded you will get this table. Also see attached QVW

Hope this helps, if not reply and I will follow up

Re: Create a custom table

amber2000 — Mon, 17 Nov 2014 09:20:01 GMT

Shawn,

Thank you so much for fixing this problem for me, it's working great

kind regards,

Ambertje

Re: Create a custom table

amber2000 — Wed, 19 Nov 2014 11:53:19 GMT

Shawn,

I discovered a little error when I present the data in this way.

1 vendor can have multiple Aisle_Desc and therefor multiple times.

I was checking if the data was correct and found inconsistency's:

This is the data

Vendor_Name	Aisle_Desc	Rec_Start_Date	Rec_Start_Time
DELPEYRAT SA	PBL_Traiteur	2/10/2014	8:25:48
DELPEYRAT SA	PBL_Zuivel	2/10/2014	9:20:12
DELPEYRAT SA	Zuivel_Gekoeld	2/10/2014	8:25:48
DELPEYRAT SA	Zuivel_Gekoeld	2/10/2014	9:20:12

This is the result in the qvw:

Vendor_Name	Aisle_Desc	Rec_Start_Date	2/10/2014
DELPEYRAT SA	PBL_Traiteur		8:25:48
DELPEYRAT SA	PBL_Zuivel		9:20:12
DELPEYRAT SA	Zuivel_Gekoeld		17:46:00

You see that it's not correct.

I think it has to do with Sum expression.

So I look for other expressions but I can't get it wright, one example:

Sum(Aggr(min(Rec_Start_Time]),[Aisle_Desc], [Rec_Start_Date]))

If I leave Sum out the result isn't correct eather.

Can you help me rewrite the statement so it will show all times for each Aisle_Desc please?

Re: Re: Create a custom table

Wed, 19 Nov 2014 15:44:21 GMT

Try this one. I uses the dual function to assign a unique value to each record.

Re: Re: Create a custom table

amber2000 — Wed, 19 Nov 2014 17:17:42 GMT

Hello Shawn,

I apologize for taking so much of your time for this issue.

So now I'm trying to implement this dual function into my script (I'll include an attach with an example of my script)

My script is getting so complex and I don't see where I must include the Dual function.

The initial example was very small but looking in the distance like the Script I'm using.

Could you please tell me where I should implement this in the new qvw?

Kind regards,

Ambertje

Re: Re: Re: Create a custom table

Wed, 19 Nov 2014 18:06:12 GMT

Hi ! I have a cat that looks like your profile pic:)

I modified your script, noted the change and added a new chart that I think will work. not real clean and snazzy but let see if this gets the job done.