Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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
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.

 

 

Highlighted
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