topic Re: Logic for a Column in QlikView

Logic for a Column

santhana — Mon, 06 May 2019 04:49:12 GMT

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

Re: Logic for a Column

Vegar — Sun, 05 May 2019 19:24:38 GMT

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.

Re: Logic for a Column

santhana — Mon, 06 May 2019 04:50:41 GMT

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

Re: Logic for a Column

jonathandienst — Mon, 06 May 2019 05:02:56 GMT

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

Re: Logic for a Column

Vegar — Mon, 06 May 2019 05:03:46 GMT

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

Re: Logic for a Column

santhana — Mon, 06 May 2019 05:09:22 GMT

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

Re: Logic for a Column

jonathandienst — Mon, 06 May 2019 05:11:19 GMT

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)

Re: Logic for a Column

Vegar — Mon, 06 May 2019 06:30:15 GMT

@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

Re: Logic for a Column

santhana — Mon, 06 May 2019 06:41:50 GMT

Thanks Vegar and Jonty..

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

Re: Logic for a Column

santhana — Wed, 22 May 2019 08:59:55 GMT

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