making table through if statment

[swiftfahad]

I have a database table like this

DemandNo	ITNo	QuotaionNO	TenderNO	PoNO
abc	xyz	s3ss3	1234	8910
def	erf	yuee	2313
ghi	yui	tt4s
jkl	tre

this is a table of purchasing system: Demand no is assign to demand (requirement) generated by user each demand is then process to different stages. IT, Quotation, Tender, purchase order.

now the value in the field identifies the stage of demand.

e.g

demand abc is complete because it has no null value.

demand def is in tender stage because tender id is assigned to it.

demand ghi is in Quotation stage

demand jkl is in IT stage..

Requirement:

now I want to show the table like

Demand	Stage	Done
abc	IT	y
abc	Quotation	y
abc	Tender	y
abc	Purchase_order	y
def	IT	y
def	Quotation	y
def	Tender	y
def	Purchase_order	n

and so on

any body can help or any body can suggest the better solution to identify the stages of a demand.?

[sunny_talwar · Accepted solution]

Try this:

Table:

CrossTable(Stage, Value)

LOAD DemandNo,

    ITNo,

    QuotaionNO,

    TenderNO,

    PoNO

FROM

[https://community.qlik.com/thread/215233]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  If(Len(Trim(Value)) = 0, 'n', 'y') as Done

Resident Table;

DROP Table Table;

[swiftfahad]

thanks bro...

what if i want to add date of each stage in final table..

suppose

DemandNo	ITNo	ITdate	QuotaionNO	QuotationDate	TenderNO	TenderDate	PoNO	PoDate
abc	xyz	04-APr-2016	s3ss3	05-Apr-2016	1234	20-Apr-2016	8910	25-Apr-2016
def	erf		yuee		2313
ghi	yui		tt4s

[sunny_talwar]

May be like this?

Table:

CrossTable(Stage, Value)

LOAD DemandNo,

    ITNo&'|'&ITdate as ITNo,

    QuotaionNO&'|'&QuotationDate as QuotaionNO,

    TenderNO&'|'&TenderDate as TenderNO,

    PoNO&'|'&PoDate as PoNO

FROM

[https://community.qlik.com/thread/215233]

(html, codepage is 1252, embedded labels, table is @3);

FinalTable:

LOAD DemandNo,

  Stage,

  SubField(Value, '|', 1) as Value,

  SubField(Value, '|', 2) as Date,

  If(Len(Trim(SubField(Value, '|', 1))) = 0, 'n', 'y') as Done

Resident Table;

DROP Table Table;

[swiftfahad]

thanks alot