topic Re: Suggest in QlikView

Suggest

Wed, 25 Nov 2020 16:16:04 GMT

After I did some kind of transfarmations using left join and creating flags my table is look like this.

Now am trying to create another flag based on the F1.

Load only first value based on the empno where F1=1

Re: Suggest

tamilarasu — Thu, 09 Mar 2017 10:22:24 GMT

Hi Babi,

I am not sure if i understood you correctly but you can try

Load *

From Table

Where F1= '1';

Re: Suggest

neha_shirsath — Thu, 09 Mar 2017 10:37:29 GMT

Hi Babi,

Can you share some sample data or please explain in detail.

Thanks & Regards,

Neha S.

Re: Suggest

Thu, 09 Mar 2017 10:38:30 GMT

It doesn't work nagaraj that's not my requirement.

My requirement is to create flag for only first value where F1=1

Based on the empno.

Pls suggest

Re: Suggest

PrashantSangle — Thu, 09 Mar 2017 10:43:55 GMT

Hi,

F1 is column name from your table

If yes then in script try like

if(F1=1,'true','False') as flag

Regards,

Prashant

Re: Suggest

neha_shirsath — Thu, 09 Mar 2017 10:46:15 GMT

Hi Babi,

You can try like-

If(F1='1', EmpNo) as Flag.

And suppress null values in your table/chart.

Thanks & Regards,

Neha S.

Re: Suggest

tamilarasu — Thu, 09 Mar 2017 11:06:24 GMT

Hello Babi,

Could you post sample input and expected output?

Re: Suggest

nagarjuna_kotha — Thu, 09 Mar 2017 11:26:30 GMT

Hi,

Like this??

Data:

LOAD * INLINE [

Customer, Typology, PartNumber, TotalAmount

customer1, typology1, 1, "316,032"

customer1, typology1, 4, "231,694"

customer1, typology1, 7, "202,305"

customer1, typology1, 1, "197,223"

customer1, typology2, 4, "163,248"

customer1, typology2, 7, "125,838"

customer1, typology2, 1, "113,576"

customer1, typology2, 4, "103,068"

customer1, typology2, 7, "90,984"

customer1, typology2, 1, "49,840"

customer2, typology1, 4, "65,039"

customer2, typology1, 7, "63,584"

customer2, typology1, 1, "22,121"

customer2, typology1, 4, "8,870"

customer2, typology1, 7, "6,944"

customer2, typology1, 1, "5,321"

customer2, typology2, 4, "5,204"

customer2, typology2, 7, "5,116"

customer2, typology2, 7, "4,211"

customer2, typology2, 1, "2,911"

customer2, typology2, 4, "2,881"

customer2, typology2, 7, 470

];

Final:

LOAD *,

'Level 1' as Flag

Resident Data Where PartNumber=1 ;

Concatenate

LOAD *,

'Level 2' as Flag

Resident Data Where PartNumber=4 ;

Concatenate

LOAD *,

'Level 3' as Flag

Resident Data Where PartNumber=7 ;

DROP Table Data ;

-Nagarjun

Re: Suggest

PrashantSangle — Thu, 09 Mar 2017 11:33:05 GMT

Hi,

why are loading same table multiple times??
Instead of that, 1 if - else statement work

Load *,if(PartNumber=1,'Level 1',if(PartNumber=4,'Level 2','Level 3')) as flag from table;

Regards,

Prashant

Re: Suggest

Thu, 09 Mar 2017 11:54:17 GMT

Can we do dynamically ???

Re: Suggest

PrashantSangle — Thu, 09 Mar 2017 12:16:12 GMT

I did not get you??

F1 is expression??

Regards,

Prashant

Re: Suggest

tamilarasu — Thu, 09 Mar 2017 12:18:04 GMT

Like this?

Data:

LOAD * INLINE [

EmpNo, DOJ, Emps, F1

10, 1/1/2017, 1, 1

10, 1/1/2017, 2, 1

10, 1/1/2017, 3, 1

20, 2/1/2017, 1, 0

20, 2/1/2017, 2, 0

20, 2/1/2017, 3, 0

30, 3/1/2017, 1, 0

30, 3/1/2017, 2, 0

30, 3/1/2017, 3, 0

40, 4/1/2017, 4, 1

40, 4/1/2017, 5, 1

40, 4/1/2017, 6, 1

50, 5/1/2017, 4, 0

50, 5/1/2017, 5, 0

50, 5/1/2017, 6, 0

];

Result:

Load *,

If(EmpNo<>Peek('EmpNo') And F1 =1,1,0) as Flag

Resident Data Order by EmpNo, Emps;

DROP Table Data;

Re: Suggest

Thu, 09 Mar 2017 12:52:48 GMT

I tell u my exact requirement

I have emp table and it contains

Company code,emp code, DOJ,

And requisition table it contains :company code

Requisition number, requisition date, Target date,num of employees

And inducted table contains

Company code, requisition number, emp code

These three tables I have

Now my requirement is

Dimensions>requisition number, requisition date and target date,

I want to calculate total head count and

Employee count if DOJ<=targetdate

And employee count DOJ>targetdate

These three I want to show in straight table.

For head count it's working fine sum(distinct num of employees)

But I strucked in remaining expressions.

Why because in the back end, I have used left joins between requisition and inducted tables and between requisition and employee table.

So that am not getting accurate results.

Any suggestions???

Am also sharing snapshots of the this scenarioplease find for your reference.