Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Maddineni_S
Contributor III
Contributor III

Data Transformation

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

1 Solution

Accepted Solutions
NitinK7
Specialist
Specialist

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

NitinK7_0-1638367331181.png

 

View solution in original post

4 Replies
Or
MVP
MVP

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
Specialist
Specialist

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

NitinK7_0-1638367331181.png

 

Maddineni_S
Contributor III
Contributor III
Author

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

NitinK7
Specialist
Specialist

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

NitinK7_0-1638429812536.png