Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP
MVP

Re: Logic for a Column

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
Highlighted
Partner
Partner

Re: Logic for a Column

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
Highlighted
Contributor III
Contributor III

Re: Logic for a Column

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

Highlighted
MVP
MVP

Re: Logic for a Column

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

Re: Logic for a Column

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
Highlighted
Contributor III
Contributor III

Re: Logic for a Column

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

 

Highlighted
MVP
MVP

Re: Logic for a Column

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

Highlighted
Partner
Partner

Re: Logic for a Column

@santhana both mine and @jontydkpi 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
Highlighted
Contributor III
Contributor III

Re: Logic for a Column

Thanks Vegar and Jonty.. 

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

 

 

Contributor III
Contributor III

Re: Logic for a Column

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