topic Re: Re: Re: Re: How show same data twice in table? in QlikView

How show same data twice in table?

Wed, 27 Nov 2013 21:05:52 GMT

I've got a pivot table with the below structure, showing tasks per country and year created. The year is gotten with dimension "=Year(CreationDate). The count is done with expression "=Count(ID)".

Country / Year	2011	2012	2013
Sweden	2	4	2
Norway	3	2	4
Finland	2	2	5

Some tasks have a status called "Cancelled". Now I wish to show these both under the year created AND under the year cancelled, see example below (ie. the 2 showing as Cancelled also appear under the year they were created).

Country / Year	2011		2012		2013
Status	Created	Cancelled	Created	Cancelled	Created	Cancelled
Sweden	2		4		2
Norway	3		2		4	2
Finland	2		2		5

I'm not sure how to accomplish this. I can easily do a sum of those with cancelled status, but they then show under the year created. I want them to remain there but also that they show under the year cancelled. Can anyone help with this please?

Attachment added

Re: How show same data twice in table?

nagaiank — Wed, 27 Nov 2013 21:24:14 GMT

Did you try using Status as the third dimension?

Re: How show same data twice in table?

Wed, 27 Nov 2013 21:27:53 GMT

Yes, I did. But then the data still show up under date created as this is the first dimension I'm using. I need it to show both under date created AND date cancelled.

Thanks for your answer still.

Re: How show same data twice in table?

jagan — Thu, 28 Nov 2013 02:39:21 GMT

Hi,

Can you attach some sample data and qlikview file.

Regards,

Jagan.

Re: Re: How show same data twice in table?

Thu, 28 Nov 2013 07:01:20 GMT

Good idea. I've included an attachment. So how can I make the table in it look like the second table in my question?

Re: Re: Re: How show same data twice in table?

bill_mtc — Thu, 28 Nov 2013 07:18:27 GMT

Hmm please see attached file. Hope it helps.

Re: Re: Re: How show same data twice in table?

Thu, 28 Nov 2013 07:33:19 GMT

Thank you for this. Unfortunately, that shows the cancelled tasks under year created. I need to show them under the year they were cancelled.

These two things need to be shown, so that each year shows how many tasks were created and how many were cancelled:

Show all tasks grouped by year created (CreationDate).
Show cancelled tasks by year cancelled (CancelDate).

Re: Re: Re: Re: How show same data twice in table?

bill_mtc — Thu, 28 Nov 2013 07:52:35 GMT

Hi Gustav,

Sorry for misunderstanding. Anyway, please see attached file again, or the script below:

/***********************************/

Table1:

LOAD * INLINE [

Country, CreationDate, CancelDate, ID, Status

Sweden, 2011, , 1

Sweden, 2011, , 2

Sweden, 2012, , 3

Sweden, 2012, , 4

Sweden, 2012, , 5

Sweden, 2012, , 6

Sweden, 2013, , 7

Sweden, 2013, , 8

Norway, 2011, , 9

Norway, 2011, , 10

Norway, 2011, 2013, 11, Cancelled

Norway, 2012, , 12,

Norway, 2012, 2013, 13, Cancelled

Norway, 2013, , 14

Norway, 2013, , 15

Norway, 2013, , 16

Norway, 2013, , 17

Finland, 2011, , 18

Finland, 2011, , 19

Finland, 2012, , 20

Finland, 2012, , 21

Finland, 2013, , 22

Finland, 2013, , 23

Finland, 2013, , 24

Finland, 2013, , 25

Finland, 2013, , 26

];

NoConcatenate

NewTable:

/*For Created ID*/

Load Country,

CreationDate as Year,

ID,

'Created' as Status

Resident Table1;

Concatenate(NewTable)

/*For Cancelled ID*/

Load Country,

CancelDate as Year,

ID,

'Cancelled' as Status

Resident Table1 where Status = 'Cancelled';

DROP Table Table1;

/***********************************/

Hope this helps! Thank you and good luck!

Best Regards,

Bill

Re: Re: Re: Re: How show same data twice in table?

jonathandienst — Thu, 28 Nov 2013 07:53:46 GMT

See attached using a date island and count(if()) logic to show the data the way you want,

HTH

Jonathan

Re: Re: Re: Re: How show same data twice in table?

Thu, 28 Nov 2013 08:12:14 GMT

Excellent, thank very much you for your swift reply! I'll remember to explain more clearly next time 🙂

Re: Re: Re: Re: How show same data twice in table?

Thu, 28 Nov 2013 08:13:33 GMT

Thanks for your reply! This example however moves the Cancelled tasks so that they are only shown under the Cancelled year, they should still be shown under Created year as well. But Bill provided a working solution, thank you both!