Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.?
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;
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;
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 |
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;
thanks alot