Can multiple columns of data be imported as one field?

[#1]

Hi all,

I'm trying to import two date fields from Excel into one QV field- is this possible? Basically I'm looking at direct debits and the data setup I have is:

Gift date - needs to be imported in all cases (showing the number of new DDs set up in a period)

Status - =Active or Cancelled

Status date - needs to be imported only when the status is Cancelled (showing how many DDs were stopped in a period)

I can do the set analysis to only take status date when it has the status I want, but I'd like both dates to be imported into one Date field in QV. I'm then assigning a value of +1 or -1 to the DD based on whether it is a new or cancelled DD and creating a bar chart showing the number of new and cancelled through time, hence I need both dates in one dimension.

Hopefully I've been clear enough about the problem- any advice gratefully received!

Hannah

[CarlosAMonroy]

Hi Hanna,

I did not understand the real issue. Can you explain more detail level or add more examples of what you need?

Thanks,

Carlos

[#3]

Hi Hannah,

I am not sure if I understood correctly, because you wrote:

- Gift date should always be displayed

- but if Status = cancelled, you want to have the StatusDate in the same field

which means that you have the gift date, if it is not cancelled (?)

Therefore, here two possible solutions

//Solution I
//result: 2 date fields, where the CancellationDate only holds the StatusDate if the status is "Cancellation"
LOAD
if(Status='Cancelled',StatusDate,null()) as CancellationDate,
GiftDate
FROM Table;

//Solution II
//result: 1 date field, where the StatusDate is used if the status is "Cancellation" and GiftDate is used in all other cases
LOAD
if(Status='Cancelled',StatusDate,GiftDate) as Date,
Status
FROM Table;

[Anonymous]

Hi chihan25,

On script, when you load Excel table:

IF(Status = 'Cancelled', "Status date","Gift Date")

If you concatenate two fields it will not be a date.

Regards

[#5 · Accepted solution]

Hi Hannah,

Have you thought about having a master calendar, with the date range going from your start until the last recorded date? Having master calendar that you then connect your different dates to gives you an option to have one dimension to work with, showing all the dates, but still gives you options to filter based on status.

Henrics post is always a good reference point for master calendar topics The Master Calendar

Good luck!

[#6]

HI

I guess it is possible to bring two columns of date fields into one use Cross Table for this

Please Try With this

[#7]

Thanks all for your advice- I think I may have muddied the water when explaining the data I have but a master calendar is the most sensible solution in this case.

Hampus, I've been putting off making a master calendar due to previous difficulties with it but I've now persevered and it seems to be doing the trick. Thanks for the recommendation!