topic Re: How to create dimension values? in QlikView

How to create dimension values?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

If the table below is my data

And this is the pivot representation of the same data:

So the question is how can I extend the status dimension such that there will be another dimension value called Rebooked in the top table or a new column in the pivot table for the accountno that has both Booked and Failure (accountno: 3000479 for example) ?

Essentially, the condition is If (status = 'Booked' AND status = 'Failure' , status = 'Rebooked').

Sorry if my question is not clear but I'm happy to elaborate on this if you guys have any idea about this case?

Many thanks,

Laleh

Re: How to create dimension values?

YoussefBelloum — Thu, 16 Nov 2017 17:01:15 GMT

Attach sample data, we will do it directly with your data

Re: How to create dimension values?

Anonymous — Thu, 16 Nov 2017 19:35:59 GMT

Attached is a sample of my data.

Re: How to create dimension values?

Anonymous — Thu, 16 Nov 2017 19:42:46 GMT

If it has a booked and failure would you want it removed from those categories and only count in the new category of 'Rebooked'?

Re: How to create dimension values?

Anonymous — Thu, 16 Nov 2017 19:45:55 GMT

Ideally we want to keep the current statuses as they are while introducing new status i.e an accountno who has both Failure & Booked will have Rebooked.

Re: How to create dimension values?

Anonymous — Thu, 16 Nov 2017 19:53:35 GMT

You probably want to do this in the script.

Something like:

Data:

Load distinct

accountno

From YourFile

where Status='Booked';

inner join(Data)

Load distinct

acountno

From YourFile

where Status='Failure';

noconcatenate

AllData:

Load

accountno,

'Rebooked' as Status

Resident Data;

Drop table Data;

concatenate(AllData)

Load accountno,

Status

From YourFile;

Re: How to create dimension values?

Anonymous — Thu, 16 Nov 2017 19:59:53 GMT

Sounds like a good idea! Thank you, I'm going to test it now, will let you know how it goes

Re: How to create dimension values?

Anonymous — Thu, 16 Nov 2017 20:17:08 GMT

well-done & thanks a lot!

Re: How to create dimension values?

vishsaggi — Thu, 16 Nov 2017 20:26:26 GMT

may be try this too.

Table1:

LOAD *, IF(AccountNo = Peek(AccountNo) AND (Match(Status, 'Booked', 'Failure') OR Match(Status, 'Failure', 'Booked')), 'Reebooked', Status) AS NewStatus;

LOAD accountno as AccountNo,

status as Status

FROM

[..\Desktop\Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: How to create dimension values?

vishsaggi — Thu, 16 Nov 2017 20:26:46 GMT

Oops too late.