How to create dimension values?

[Anonymous]

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

[YoussefBelloum]

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

[Anonymous]

Attached is a sample of my data.

[Anonymous]

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

[Anonymous]

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.

[Anonymous · Accepted solution]

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;

[Anonymous]

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

[Anonymous]

well-done & thanks a lot!

[vishsaggi]

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);

[vishsaggi]

Oops too late.