Data Transformation

[Maddineni_S]

ID	Open	Close
1	11/30/2021	12/01/2021
2	11/30/2021	12/01/2021
3	12/01/2021	-
4	12/01/2021	-

i want to transform above table data as looks below, can some one guide me. ??

ID	Date	Status
1	11/30/2021	Open
1	12/01/2021	Close
2	11/30/2021	Open
2	12/01/2021	Close
3	12/01/2021	Open
4	12/01/2021	Open

[Or]

Try:

Load ID, Open as Date, 'Open' as Status

From YourTable

Where not isnull(Open);

CONCATENATE

Load ID, Close as Date, 'Close' as Status

From YourTable

Where Not isnull(Close);

If your data is on a larger scale rather than just open and close, have a look at the Crosstable command for a more robust approach

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

[NitinK7 · Accepted solution]

try to use cross table

ABC:
CrossTable(Status,Date)
LOAD
ID,
Open,
Close
FROM [lib://Desktop/qlik sense - XIRR.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Maddineni_S]

but i have null cells in my date column i have to ignore them ??

[NitinK7]

you add '-' instead of blank just because of that you can see null cells .

if you keep blank without any sign(-) then blank row will remove