Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
santhana
Contributor III
Contributor III

Logic for a Column

 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

 

1 Solution

Accepted Solutions
jonathandienst

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)
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

9 Replies
Vegar
Partner
Partner

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.

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
santhana
Contributor III
Contributor III
Author

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

jonathandienst

>>... 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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Vegar
Partner
Partner

Try something like this:
Load
ID,
Status
1 as Indicator
From EXCEL
Where Status='Booked'
;
Load
ID,
Status
0 as Indicator
From EXCEL
Where EXISTS(ID) and
Status<>'Booked'
;
Load
ID,
Status
'blank' as Indicator
From EXCEL
Where not EXISTS(ID)
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
santhana
Contributor III
Contributor III
Author

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

 

jonathandienst

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)
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Vegar
Partner
Partner

@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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
santhana
Contributor III
Contributor III
Author

Thanks Vegar and Jonty.. 

  Really appreciate your quick help in understanding the required result and providing the solution.

 

 

santhana
Contributor III
Contributor III
Author

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