Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables: transactions and platform. Here's a sample:
transactions:
LOAD * INLINE [
transaction, site, platform
1, 1, 0
2, 1, 1
3, 1, 1
4, 2, 0
5, 2, 0
6, 2, 0
7, 3, 1
8, 3, 0
;
platform:
LOAD * INLINE [
platform, platform_name
0, new
1, old
;
I would like to limit the transactions table to only include sites which have transactions on both platforms , 0 and 1. In this case, I would only import data for sites 1 adn 3, since site 2 only has sales on plaform 0. Does anyone know how I can specify this in my import script?
Thanks in advance for the help, it is very appreciated. I've attached a qvw with the sample data shown above.
Best,
Matt
Hi Matt,
Try this script, hope this helps you.
Transactions:
LOAD * INLINE [
transaction, site, platform
1, 1, 0
2, 1, 1
3, 1, 1
4, 2, 0
5, 2, 0
6, 2, 0
7, 3, 1
8, 3, 0
9, 3, 0
10, 3, 0
];
INNER JOIN (Transactions)
LOAD
site
WHERE platformcount > 1;
LOAD
site,
Count(Distinct platform) as platformcount
RESIDENT Transactions
GROUP BY site;
platform:
LOAD * INLINE [
platform, platform_name
0, new
1, old
];
Regards,
Jagan.
Hi,
Please find the attached modified qvw file.
HTH
Sushil
Hi,
Have a look at the attachment.
Regards,
Kaushik Solanki
Hi Matt,
Try this script, hope this helps you.
Transactions:
LOAD * INLINE [
transaction, site, platform
1, 1, 0
2, 1, 1
3, 1, 1
4, 2, 0
5, 2, 0
6, 2, 0
7, 3, 1
8, 3, 0
9, 3, 0
10, 3, 0
];
INNER JOIN (Transactions)
LOAD
site
WHERE platformcount > 1;
LOAD
site,
Count(Distinct platform) as platformcount
RESIDENT Transactions
GROUP BY site;
platform:
LOAD * INLINE [
platform, platform_name
0, new
1, old
];
Regards,
Jagan.
Hi Matt,
there's another solution
transactions:
LOAD * INLINE [
transaction, site, platform
1, 1, 0
2, 1, 1
3, 1, 1
4, 2, 0
5, 2, 0
6, 2, 0
7, 3, 1
8, 3, 0
];
platform:
LOAD * INLINE [
platform, platform_name
0, new
1, old
];
t1:
LOAD distinct site
Resident transactions
Where platform = 0;
Right Join (t1)
LOAD distinct site
Resident transactions
Where platform = 1;
Right Join (transactions)
LOAD site
Resident t1;
DROP Table t1;
Best Regards,
SV
Thanks to all for the help. All of these methods worked. Based on what I'm doing, Jagan's will be the best for the moment.
Thanks again, the help was much appreciated.
Matt