Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends
I have 2 columns Status and ID.
ID is not unique , it repeats with different Status.
When Status is Booked , I need to put '1' in new column- Indicator . and 0 for all other status having the same ID.
If status is other then Booked , then Blank as Indicator.
(Basically it is like a V look Up of Excel. Keeping all the ID;s which are Booked in one column and searching for the same ID's in entire ID column for any matches. But I am finding difficult to implement in QlikView)
I have attached raw data and output in separate sheet in the excel.
Thanks in advance for help.
Regards
Santhana
Like this perhaps:
MAP_BOOKED:
Mapping Load ID, 0
From Test_new.xlsx
(ooxml, embedded labels, table is Raw)
Where Status = 'Booked';
RESULT:
LOAD ID,
Status,
If(Status = 'Booked', 1,
ApplyMap('MAP_BOOKED', ID, null())
) as Indicator
FROM Test_new.xlsx
(ooxml, embedded labels, table is Raw)
Did you attach the correct excel sample file? If not, then please explain your data. The ID /Ticket No seems unique and you have no Status column containing the value Booked.
Dear Vegar
Thanks for your revert and pointing out the error.
My bad. I had uploaded the wrong excel. I have attached the correct one now named Test_new.
Thanks & Regards
Santana
>>... 0 for all other status having the same ID.
>>...If status is other then Booked , then Blank as Indicator.
I am not quite sure what you mean. The blanks are for IDs that never have a booked status, and the zeroes are for IDs that have a booked status?
The blanks are for IDs that never have a booked status, and the zeroes are for IDs that have a booked status.
Exactly as above...
Like this perhaps:
MAP_BOOKED:
Mapping Load ID, 0
From Test_new.xlsx
(ooxml, embedded labels, table is Raw)
Where Status = 'Booked';
RESULT:
LOAD ID,
Status,
If(Status = 'Booked', 1,
ApplyMap('MAP_BOOKED', ID, null())
) as Indicator
FROM Test_new.xlsx
(ooxml, embedded labels, table is Raw)
@santhana both mine and @jonathandienst solutions should give you the same output. If we understood your problem correctly please mark the question solved by marking one or both as a correct answer.
If we did not understand correctly please make a comment and we'll try to adjust our suggestions.
-Vegar
Thanks Vegar and Jonty..
Really appreciate your quick help in understanding the required result and providing the solution.
Hello Jonty/Vegar
I need to identify Mobile numbers as Repeat if they were available upto end of previous quarter.
Example: if mobile no in May is also available before 31st March (upto last Quarter) then it is repeat. Else fresh.
I am able to achieve the above , by using below code. However its not dynamic for previous selections. Is there any way around to get it for previous years as well. Eg: if we select May 2018 , it should search upto Mar 31st ,2018
Code:
Mapp_Qtr:
Mapping
Load
Mobile_No,
'Repeat'
From
[C:\.......\QVD\Final\Final.qvd]
(qvd) where Date<=QuarterStart(today());
LOAD
Lead,
Status,
Quarter,
Key,
Month,
if( Date>=QuarterStart(today()) and Date<=QuarterEnd(today()), ApplyMap('Mapp_Qtr',Mobile_No,'Fresh')) as Lead_Type,
from
From
[C:\.......\QVD\Final\Final.qvd]
(qvd);
Thanks & Regards
Santhana