Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a column called Test Dates which contains dates for Patients tests but then I have two other columns with Entry and exit date of the patient. I want to create one column per patient ID that contains all the dates in one column. so Entry Date, CT Scan, MRI and Exit date. I am attaching a dummy data set and how I want it to look like. Any help is much appreciated!
Thanks!
@JonesBeach try below
Data:
LOAD Distinct
"Patient ID",
"Entry Date" as [Merged Date],
'Entry Date' as [Tests Done]
FROM [lib://Files/Patient Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Concatenate(Data)
LOAD Distinct
"Patient ID",
"Tests Done",
"Entry Date" as [Merged Date]
FROM [lib://Files/Patient Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Concatenate(Data)
LOAD Distinct
"Patient ID",
"Exit Date" as [Merged Date],
'Exit Date' as [Tests Done]
FROM [lib://Files/Patient Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
I would use a crosstable transformation when loading the data:
Crosstable ('Date Type', Date, 2)
Load [Patient ID], [Test Done], [Entry Date], [Test Dates], [Exit Date] From ...
Then you will get all dates in one column called [Date], and can choose to show either [Test Done] or [Date Type].
See more on
https://community.qlik.com/t5/Design/The-Crosstable-Load/ba-p/1468083
@JonesBeach try below
Data:
LOAD Distinct
"Patient ID",
"Entry Date" as [Merged Date],
'Entry Date' as [Tests Done]
FROM [lib://Files/Patient Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Concatenate(Data)
LOAD Distinct
"Patient ID",
"Tests Done",
"Entry Date" as [Merged Date]
FROM [lib://Files/Patient Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Concatenate(Data)
LOAD Distinct
"Patient ID",
"Exit Date" as [Merged Date],
'Exit Date' as [Tests Done]
FROM [lib://Files/Patient Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Big help Kushal thanks so much for putting in time towards this.
Peace!